<?php

  function get_thu_quy_theo_ma($ma_thu_quy) {
    $query = db_select('dt_thu_quy', 'tq');
    $query->fields('tq', array('id', 'ma_thu_quy', 'ten_thu_quy', 'loai', 'trang_thai', 'thoi_gian'));
    $query->condition('tq.ma_thu_quy', $ma_thu_quy, '=');
    $result = $query->execute()->fetchObject();
    return $result;
  }

  function insert_thuquy($uid, $ten_thu_quy, $loai, $dia_chi, $so_dien_thoai, $tien_no) {
    $thoi_gian = time();
    $trang_thai = 1;
    $query = db_insert('dt_thu_quy');
    $arr = array(
      'uid' => $uid,
      'ma_thu_quy' => '',
      'ten_thu_quy' => $ten_thu_quy,
      'trang_thai' => $trang_thai,
      'tong_tien' => $tien_no,
      'loai' => $loai,
      'dia_chi' => $dia_chi,
      'so_dien_thoai' => $so_dien_thoai,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_thu_quy = $id;
    while (strlen($ma_thu_quy) < 3) {
      $ma_thu_quy = '0' . $ma_thu_quy;
    }
    $ma_thu_quy = 'TQ_' . $ma_thu_quy;
    db_update('dt_thu_quy')
      ->fields(array(
                    'ma_thu_quy' => $ma_thu_quy,
               ))
      ->condition('id', $id)
      ->execute();
    $query_n = db_insert('dt_thu_quy_du_lieu');
    $arr_n = array(
      'uid' => $uid,
      'ma_thu_quy' => $ma_thu_quy,
      'tong_tien' => $tien_no,
      'thoi_gian' => $thoi_gian
    );
    $query_n->fields(array_keys($arr_n));
    $query_n->values($arr_n);
    $query_n->execute();
    return $ma_thu_quy;
  }

  function insert_ma_phieu_chuyen($uid, $thu_quy, $ban_hang, $tong_tien, $ghi_chu) {
    $thoi_gian = time();
    $query = db_insert('dt_phieu_chuyen_tien_hang');
    $arr = array(
      'uid' => $uid,
      'ma_phieu_chuyen' => '',
      'ma_thu_quy_nhan' => $thu_quy,
      'ma_nhan_vien_ban_hang' => $ban_hang,
      'tong_tien' => $tong_tien,
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_phieu_chuyen = $id;
    while (strlen($ma_phieu_chuyen) < 3) {
      $ma_phieu_chuyen = '0' . $ma_phieu_chuyen;
    }
    $ma_phieu_chuyen = 'PTT_' . $ma_phieu_chuyen;
    db_update('dt_phieu_chuyen_tien_hang')
      ->fields(array(
                    'ma_phieu_chuyen' => $ma_phieu_chuyen,
               ))
      ->condition('id', $id)
      ->execute();
    return $ma_phieu_chuyen;
  }

  function tong_tien_thanh_toan($ma_thu_quy) {
    $query_n = db_select('dt_phieu_thu_thanh_pham', 'n');
    $query_n->condition('thanh_toan', 0);
    $query_n->condition('ma_thu_quy', $ma_thu_quy);
    $query_n->fields('n', array('id', 'ma_phieu_thu', 'tong_tien', 'thanh_toan', 'thoi_gian'));
    $query_x = db_select('dt_phieu_thu_le', 'x');
    $query_x->condition('thanh_toan', 0);
    // $query_x->condition('ma_nha_cung_cap', '');
    $query_x->condition('ma_thu_quy', $ma_thu_quy);
    $query_x->fields('x', array('id', 'ma_phieu_thu', 'tong_tien', 'thanh_toan', 'thoi_gian'));
    $query_n->union($query_x, 'UNION ALL');
    $query = db_select($query_n, 'pt')->extend('PagerDefault');
    $query->fields('pt', array('id', 'ma_phieu_thu', 'tong_tien', 'thanh_toan', 'thoi_gian'));
    $query->addExpression('SUM(tong_tien)', 'tong_so_tien');
    $result = $query->execute();
    while ($rs = $result->fetchObject()) {
      $data = $rs->tong_so_tien;
    }
    return $data;
  }

  function update_phieu_thu($ma_phieu_chuyen) {
    //  update_tien_du('-', $so_tien, $ma_thu_quy)
    db_update('dt_phieu_thu_thanh_pham')
      ->fields(array(
                    'ma_phieu_chuyen' => $ma_phieu_chuyen,
                    'thanh_toan' => 1,
               ))
      ->condition('thanh_toan', 0)
      ->execute();
    db_update('dt_phieu_thu_le')
      ->fields(array(
                    'ma_phieu_chuyen' => $ma_phieu_chuyen,
                    'thanh_toan' => 1,
               ))
      ->condition('thanh_toan', 0)
    //->condition('ma_nha_cung_cap', '')
      ->execute();
  }

  function load_select_thu_quy() {
    $list_loai = array();
    $loai = 0;
    $result = db_query("SELECT ma_thu_quy,ten_thu_quy from {dt_thu_quy} where loai=:loai", array(':loai' => $loai));
    foreach ($result as $row)
    {
      $list_loai[$row->ma_thu_quy] = $row->ten_thu_quy;
    }
    return $list_loai;
  }

  function load_select_ban_hang() {
    $list_loai = array();
    $loai = 1;
    $result = db_query("SELECT ma_thu_quy,ten_thu_quy from {dt_thu_quy} where loai=:loai", array(':loai' => $loai));
    foreach ($result as $row)
    {
      $list_loai[$row->ma_thu_quy] = $row->ten_thu_quy;
    }
    return $list_loai;
  }

  function get_default_nhan_vien_ban_hang() {
    $query = db_select('dt_thu_quy', 'tq');
    $query->fields('tq', array('ma_thu_quy'));
    $query->condition('loai', 1);
    $result = $query->execute();
    while ($rs = $result->fetchObject()) {
      $data = $rs->ma_thu_quy;
    }
    return $data;
  }

  function insert_phieu_chi($uid, $nha_cung_cap, $ma_thu_quy, $so_tien, $nguoi_nhan, $ghi_chu) {
    $thoi_gian = time();
    $last_b = strrpos($nha_cung_cap, '[') + 1;
    $last_e = strrpos($nha_cung_cap, ']');
    $ma_nha_cung_cap = substr($nha_cung_cap, $last_b, $last_e - $last_b);
    $query = db_insert('dt_phieu_chi_vat_tu');
    $arr = array(
      'uid' => $uid,
      'ma_phieu_chi' => '',
      'ma_nha_cung_cap' => $ma_nha_cung_cap,
      'ma_thu_quy' => $ma_thu_quy,
      'tong_tien' => $so_tien,
      'nguoi_nhan' => $nguoi_nhan,
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_phieu_chi = $id;
    while (strlen($ma_phieu_chi) < 3) {
      $ma_phieu_chi = '0' . $ma_phieu_chi;
    }
    $ma_phieu_chi = 'PC_' . $ma_phieu_chi;
    db_update('dt_phieu_chi_vat_tu')
      ->fields(array(
                    'ma_phieu_chi' => $ma_phieu_chi,
               ))
      ->condition('id', $id)
      ->execute();
    update_tien_du('-', $so_tien, $ma_thu_quy);
    return $ma_phieu_chi;
  }

  function update_phieu_chi($ma_phieu, $uid, $nha_cung_cap, $ma_thu_quy, $so_tien, $nguoi_nhan, $ghi_chu, $time) {
    $thoi_gian = $time;
    $last_b = strrpos($nha_cung_cap, '[') + 1;
    $last_e = strrpos($nha_cung_cap, ']');
    $ma_nha_cung_cap = substr($nha_cung_cap, $last_b, $last_e - $last_b);
    db_update('dt_phieu_chi_vat_tu')
      ->fields(array(
                    'uid' => $uid,
                    'ma_nha_cung_cap' => $ma_nha_cung_cap,
                    'ma_thu_quy' => $ma_thu_quy,
                    'tong_tien' => $so_tien,
                    'nguoi_nhan' => $nguoi_nhan,
                    'ghi_chu' => $ghi_chu,
                    'thoi_gian' => $thoi_gian,
               ))
      ->condition('ma_phieu_chi', $ma_phieu)
      ->execute();
  }

  function insert_phieu_chi_le($uid, $ma_thu_quy, $so_tien, $nguoi_nhan, $ghi_chu, $ma_khach_hang) {
    $thoi_gian = time();
    $query = db_insert('dt_phieu_chi_le');
    $arr = array(
      'uid' => $uid,
      'ma_phieu_chi' => '',
      'ma_thu_quy' => $ma_thu_quy,
      'ma_khach_hang' => $ma_khach_hang,
      'tong_tien' => $so_tien,
      'nguoi_nhan' => $nguoi_nhan,
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_phieu_chi = $id;
    while (strlen($ma_phieu_chi) < 3) {
      $ma_phieu_chi = '0' . $ma_phieu_chi;
    }
    $ma_phieu_chi = 'PCL_' . $ma_phieu_chi;
    db_update('dt_phieu_chi_le')
      ->fields(array(
                    'ma_phieu_chi' => $ma_phieu_chi,
               ))
      ->condition('id', $id)
      ->execute();
    update_tien_du('-', $so_tien, $ma_thu_quy);
    return $ma_phieu_chi;
  }

  function update_phieu_chi_le($ma_phieu, $uid, $ma_thu_quy, $so_tien, $nguoi_nhan, $ghi_chu, $ma_khach_hang, $thoi_gian) {
    db_update('dt_phieu_chi_le')
      ->fields(array(
                    'uid' => $uid,
                    'ma_thu_quy' => $ma_thu_quy,
                    'ma_khach_hang' => $ma_khach_hang,
                    'tong_tien' => $so_tien,
                    'nguoi_nhan' => $nguoi_nhan,
                    'ghi_chu' => $ghi_chu,
                    'thoi_gian' => $thoi_gian
               ))
      ->condition('ma_phieu_chi', $ma_phieu)
      ->execute();
  }

  function insert_phieu_trich_quy($uid, $ma_thu_quy, $so_tien, $nguoi_nhan, $ghi_chu) {
    $thoi_gian = time();
    $query = db_insert('dt_phieu_trich_quy');
    $arr = array(
      'uid' => $uid,
      'ma_phieu_trich' => '',
      'ma_thu_quy' => $ma_thu_quy,
      'tong_tien' => $so_tien,
      'nguoi_nhan' => $nguoi_nhan,
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_phieu_chi = $id;
    while (strlen($ma_phieu_chi) < 3) {
      $ma_phieu_chi = '0' . $ma_phieu_chi;
    }
    $ma_phieu_chi = 'PTQ_' . $ma_phieu_chi;
    db_update('dt_phieu_trich_quy')
      ->fields(array(
                    'ma_phieu_trich' => $ma_phieu_chi,
               ))
      ->condition('id', $id)
      ->execute();
    update_tien_du('-', $so_tien, $ma_thu_quy);
    return $ma_phieu_chi;
  }

  function update_phieu_trich_quy($ma_phieu, $uid, $ma_thu_quy, $so_tien, $nguoi_nhan, $ghi_chu, $thoi_gian) {
    db_update('dt_phieu_trich_quy')
      ->fields(array(
                    'uid' => $uid,
                    'ma_thu_quy' => $ma_thu_quy,
                    'tong_tien' => $so_tien,
                    'nguoi_nhan' => $nguoi_nhan,
                    'ghi_chu' => $ghi_chu,
                    'thoi_gian' => $thoi_gian
               ))
      ->condition('ma_phieu_trich', $ma_phieu)
      ->execute();
  }

  function insert_phieu_thu($uid, $khach_hang, $ma_thu_quy, $so_tien, $so_tien_chua_giam, $nguoi_nop, $ghi_chu, $loai_tien) {
    $thanh_toan = 0;
    $thoi_gian = time();
    $last_b = strrpos($khach_hang, '[') + 1;
    $last_e = strrpos($khach_hang, ']');
    $ma_khach_hang = substr($khach_hang, $last_b, $last_e - $last_b);
    $query = db_insert('dt_phieu_thu_thanh_pham');
    $arr = array(
      'uid' => $uid,
      'ma_phieu_thu' => '',
      'ma_khach_hang' => $ma_khach_hang,
      'ma_thu_quy' => $ma_thu_quy,
      'tong_tien_chua_giam' => $so_tien_chua_giam,
      'tong_tien' => $so_tien,
      'tien_bot' => 0,
      'nguoi_nop' => $nguoi_nop,
      'thanh_toan' => $thanh_toan,
      'ma_phieu_chuyen' => '',
      'ma_loai_tien' => $loai_tien,
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_phieu_thu = $id;
    while (strlen($ma_phieu_thu) < 3) {
      $ma_phieu_thu = '0' . $ma_phieu_thu;
    }
    $ma_phieu_thu = 'PT_' . $ma_phieu_thu;
    db_update('dt_phieu_thu_thanh_pham')
      ->fields(array(
                    'ma_phieu_thu' => $ma_phieu_thu,
               ))
      ->condition('id', $id)
      ->execute();
    update_tien_du('+', $so_tien, $ma_thu_quy);
    update_tien_bot_thu_tp($ma_phieu_thu);
    return $ma_phieu_thu;
  }

  function check_trung_phieu_thu_thanh_pham($uid, $khach_hang, $ma_thu_quy, $so_tien, $so_tien_chua_giam, $nguoi_nop, $ghi_chu, $loai_tien) {
    $thanh_toan = 0;
    $last_b = strrpos($khach_hang, '[') + 1;
    $last_e = strrpos($khach_hang, ']');
    $ma_khach_hang = substr($khach_hang, $last_b, $last_e - $last_b);
    $thoi_gian = time();
    $query = db_select('dt_phieu_thu_thanh_pham', 'n');
    $query->fields('n', array('id'));
    $query->condition('uid', $uid);
    $query->condition('ma_khach_hang', $ma_khach_hang);
    $query->condition('ma_thu_quy', $ma_thu_quy);
    $query->condition('tong_tien_chua_giam', $so_tien_chua_giam);
    $query->condition('tong_tien', $so_tien);
    $query->condition('nguoi_nop', $nguoi_nop);
    $query->condition('thanh_toan', $thanh_toan);
    $query->condition('ma_loai_tien', $loai_tien);
    $query->condition('ghi_chu', $ghi_chu);
    $query->condition('thoi_gian', $thoi_gian - 300, '>');
    $result = $query->execute()->fetchObject();
    return $result;
  }

  function check_trung_phieu_thu_le($uid, $so_tien, $so_tien_chua_giam, $ma_thu_quy, $nguoi_nop, $ghi_chu, $ma_nha_cung_cap) {
    $thanh_toan = 0;
    $thoi_gian = time();
    $ma_loai_tien = 1;
    $query = db_select('dt_phieu_thu_le', 'n');
    $query->fields('n', array('id'));
    $query->condition('uid', $uid);
    $query->condition('ma_nha_cung_cap', $ma_nha_cung_cap);
    $query->condition('tong_tien_chua_giam', $so_tien_chua_giam);
    $query->condition('tong_tien', $so_tien);
    $query->condition('ma_thu_quy', $ma_thu_quy);
    $query->condition('nguoi_nop', $nguoi_nop);
    $query->condition('thanh_toan', $thanh_toan);
    $query->condition('ma_loai_tien', $ma_loai_tien);
    $query->condition('ghi_chu', $ghi_chu);
    $query->condition('thoi_gian', $thoi_gian - 300, '>');
    $result = $query->execute()->fetchObject();
    return $result;
  }

  function update_phieu_thu_thanh_pham($ma_phieu, $uid, $khach_hang, $so_tien, $so_tien_chua_giam, $nguoi_nop, $ghi_chu, $loai_tien, $thoi_gian) {
    $last_b = strrpos($khach_hang, '[') + 1;
    $last_e = strrpos($khach_hang, ']');
    $ma_khach_hang = substr($khach_hang, $last_b, $last_e - $last_b);
    db_update('dt_phieu_thu_thanh_pham')
      ->fields(array(
                    'uid' => $uid,
                    'ma_khach_hang' => $ma_khach_hang,
                    'tong_tien_chua_giam' => $so_tien_chua_giam,
                    'tong_tien' => $so_tien,
                    'nguoi_nop' => $nguoi_nop,
                    'ma_loai_tien' => $loai_tien,
                    'ghi_chu' => $ghi_chu,
                    'thoi_gian' => $thoi_gian
               ))
      ->condition('ma_phieu_thu', $ma_phieu)
      ->execute();
    update_tien_bot_thu_tp($ma_phieu);
  }

  function insert_phieu_thu_le($uid, $so_tien, $so_tien_chua_giam, $ma_thu_quy, $nguoi_nop, $ghi_chu, $ma_nha_cung_cap) {
    $thanh_toan = 0;
    $thoi_gian = time();
    $ma_loai_tien = 1;
    $query = db_insert('dt_phieu_thu_le');
    $arr = array(
      'uid' => $uid,
      'ma_phieu_thu' => '',
      'ma_nha_cung_cap' => $ma_nha_cung_cap,
      'tong_tien_chua_giam' => $so_tien_chua_giam,
      'tong_tien' => $so_tien,
      'tien_bot' => 0,
      'ma_thu_quy' => $ma_thu_quy,
      'nguoi_nop' => $nguoi_nop,
      'thanh_toan' => $thanh_toan,
      'ghi_chu' => $ghi_chu,
      'ma_phieu_chuyen' => '',
      'ma_loai_tien' => $ma_loai_tien,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_phieu_thu = $id;
    while (strlen($ma_phieu_thu) < 3) {
      $ma_phieu_thu = '0' . $ma_phieu_thu;
    }
    $ma_phieu_thu = 'PTL_' . $ma_phieu_thu;
    db_update('dt_phieu_thu_le')
      ->fields(array(
                    'ma_phieu_thu' => $ma_phieu_thu,
               ))
      ->condition('id', $id)
      ->execute();
    //if ($ma_nha_cung_cap == '') {
    update_tien_du('+', $so_tien, $ma_thu_quy);
    // }
    update_tien_bot_thu_le($ma_phieu_thu);
    return $ma_phieu_thu;
  }

  function update_phieu_thu_le($ma_phieu, $uid, $so_tien, $so_tien_chua_giam, $nguoi_nop, $ghi_chu, $ma_nha_cung_cap, $thoi_gian) {
    db_update('dt_phieu_thu_le')
      ->fields(array(
                    'uid' => $uid,
                    'ma_nha_cung_cap' => $ma_nha_cung_cap,
                    'tong_tien_chua_giam' => $so_tien_chua_giam,
                    'tong_tien' => $so_tien,
                    'nguoi_nop' => $nguoi_nop,
                    'ghi_chu' => $ghi_chu,
                    'thoi_gian' => $thoi_gian
               ))
      ->condition('ma_phieu_thu', $ma_phieu)
      ->execute();
    update_tien_bot_thu_le($ma_phieu);
  }

  function insert_phieu_chuyen_tien($uid, $ma_thu_quy_chi, $ma_thu_quy_nhan, $so_tien, $ghi_chu) {
    $thoi_gian = time();
    $query = db_insert('dt_giao_tien');
    $arr = array(
      'uid' => $uid,
      'ma_phieu_chuyen' => '',
      'ma_thu_quy_chi' => $ma_thu_quy_chi,
      'ma_thu_quy_nhan' => $ma_thu_quy_nhan,
      'tong_tien' => $so_tien,
      'thoi_gian' => $thoi_gian,
      'ghi_chu' => $ghi_chu
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_phieu_chuyen = $id;
    while (strlen($ma_phieu_chuyen) < 3) {
      $ma_phieu_chuyen = '0' . $ma_phieu_chuyen;
    }
    $ma_phieu_chuyen = 'PC_' . $ma_phieu_chuyen;
    db_update('dt_giao_tien')
      ->fields(array(
                    'ma_phieu_chuyen' => $ma_phieu_chuyen,
               ))
      ->condition('id', $id)
      ->execute();
    update_tien_du('-', $so_tien, $ma_thu_quy_chi);
    update_tien_du('+', $so_tien, $ma_thu_quy_nhan);
    return $ma_phieu_chuyen;
  }

  function update_phieu_chuyen_tien($ma_phieu, $uid, $ma_thu_quy_chi, $ma_thu_quy_nhan, $so_tien, $ghi_chu, $thoi_gian) {
    db_update('dt_giao_tien')
      ->fields(array(
                    'uid' => $uid,
                    'ma_thu_quy_chi' => $ma_thu_quy_chi,
                    'ma_thu_quy_nhan' => $ma_thu_quy_nhan,
                    'tong_tien' => $so_tien,
                    'thoi_gian' => $thoi_gian,
                    'ghi_chu' => $ghi_chu
               ))
      ->condition('ma_phieu_chuyen', $ma_phieu)
      ->execute();
  }

  function get_phieu_ban_hang($header, $ma_thu_quy) {
    $query_n = db_select('dt_phieu_thu_thanh_pham', 'n');
    $query_n->condition('thanh_toan', 0);
    $query_n->condition('ma_thu_quy', $ma_thu_quy);
    $query_n->fields('n', array('id', 'ma_phieu_thu', 'tong_tien', 'thanh_toan', 'thoi_gian', 'ma_loai_tien'));
    $query_n->addExpression("'Phiếu thu tiền hàng'", 'type');
    $query_n->leftJoin('dt_khach_hang', 'kd', 'kd.ma_khach_hang = n.ma_khach_hang');
    $query_n->addField('kd', 'ten_khach_hang', 'khach_hang');
    $query_x = db_select('dt_phieu_thu_le', 'x');
    $query_x->condition('thanh_toan', 0);
    $query_x->condition('ma_thu_quy', $ma_thu_quy);
    //$query_x->condition('ma_nha_cung_cap', '');
    $query_x->fields('x', array('id', 'ma_phieu_thu', 'tong_tien', 'thanh_toan', 'thoi_gian', 'ma_loai_tien'));
    $query_x->addExpression("'Phiếu thu lẻ'", 'type');
    $query_x->addField('x', 'nguoi_nop', 'khach_hang');
    $query_n->union($query_x, 'UNION ALL');
    $query = db_select($query_n, 'pt')->extend('PagerDefault');
    $query->fields('pt', array('id', 'ma_phieu_thu', 'tong_tien', 'thanh_toan', 'thoi_gian', 'ma_loai_tien', 'type', 'khach_hang'));
    $result = $query->orderBy('ma_loai_tien', 'DESC')->limit(20)->execute();
    $data = array();
    $loai_tien = get_option_loai_tien();
    while ($rs = $result->fetchObject()) {
      $str = explode("_", $rs->ma_phieu_thu);
      if ($str[0] == 'PT') {
        $type = 'thusanpham';
      }
      else
      {
        $type = 'thule';
      }
      $data[] = array(
        'ma_phieu_thu' => $rs->ma_phieu_thu,
        'type' => $rs->type,
        'khach_hang' => $rs->khach_hang,
        'tong_tien' => number_format($rs->tong_tien),
        'ma_loai_tien' => $loai_tien[$rs->ma_loai_tien],
        'thanh_toan' => $rs->thanh_toan ? 'Đã chuyển tiền' : 'Chưa chuyển tiền',
        'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
        'thao_tac' => l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/' . $type . '/' . $rs->ma_phieu_thu)
      );
    }
    return $data;
  }

  function get_phieu_thu_chi($type, $start_month, $end_month) {
    switch ($type) {
      case 'thusanpham':
        $query = db_select('dt_phieu_thu_thanh_pham', 'ttp')->extend('TableSort');
        $query->fields('ttp', array('id', 'ma_phieu_thu', 'ma_khach_hang', 'ma_thu_quy', 'tong_tien_chua_giam', 'tong_tien', 'tien_bot', 'nguoi_nop', 'thanh_toan', 'thoi_gian'));
        $query->condition('ttp.thoi_gian', $start_month, '>=');
        $query->condition('ttp.thoi_gian', $end_month, '<=');
        $query->leftJoin('dt_khach_hang', 'kd', 'kd.ma_khach_hang = ttp.ma_khach_hang');
        $query->addField('kd', 'ten_khach_hang');
        $query->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = ttp.ma_thu_quy');
        $query->addField('tq', 'ten_thu_quy');
        // $query->orderByHeader($header);
        $result = $query->orderBy('ttp.thoi_gian', 'DESC')->execute();
        $data = array();
        $tong_tien_chua_giam = 0;
        $tong_tien = 0;
        $tien_bot = 0;
        $admin_perm = user_access('admin xoa quy');
        $admin_perm_edit = user_access('admin sua quy');
        $header = array(
          'Mã phiếu thu',
          'Khách hàng',
          'Số tiền trước giảm',
          'Số tiền thu',
          'Tiền bớt',
          'Thủ quỹ thu',
          'Thanh toán',
          'Ngày tháng',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $output = '<a href="../quy_doanh_nghiep/phieu_thu_chi/xoa_phieu/' . $type . '/' . $rs->ma_phieu_thu . '" onclick="return check_xoa();">Xóa</a>';
          $tong_tien_chua_giam += $rs->tong_tien_chua_giam;
          $tong_tien += $rs->tong_tien;
          $tien_bot += $rs->tien_bot;
          $data[] = array(
//            'ma_phieu_thu' => $rs->ma_phieu_thu,
//            'ma_khach_hang' => $rs->ten_khach_hang,
//            'tong_tien_chua_giam' => number_format($rs->tong_tien_chua_giam),
//            'tong_tien' => number_format($rs->tong_tien),
//            'tien_bot' => number_format($rs->tien_bot),
//            'ma_thu_quy' => $rs->ten_thu_quy,
//            'thanh_toan' => $rs->thanh_toan ? 'Đã chuyển tiền' : 'Chưa chuyển tiền',
//            'thoi_gian' => date('d/m/Y', $rs->thoi_gian),
//            'thao_tac' => l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/' . $type . '/' . $rs->ma_phieu_thu)
            array('data' => $rs->ma_phieu_thu, 'class' => array('text-center')),
            array('data' => $rs->ten_khach_hang, 'class' => array('text-center')),
            array('data' => number_format($rs->tong_tien_chua_giam), 'class' => array('text-center')),
            array('data' => number_format($rs->tong_tien), 'class' => array('text-center')),
            array('data' => number_format($rs->tien_bot), 'class' => array('text-center')),
            array('data' => $rs->ten_thu_quy, 'class' => array('text-center')),
            array('data' => $rs->thanh_toan ? 'Đã chuyển tiền' : 'Chưa chuyển tiền', 'class' => array('text-center')),
            array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('text-center')),
            ($admin_perm ? ($output . ' | ')
              : '') .
            ($admin_perm_edit
              ? (l('Sửa', 'quy_doanh_nghiep/thu_chi/sua_phieu/' . $type . '/' . $rs->ma_phieu_thu) . ' | ')
              : '') . l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/' . $type . '/' . $rs->ma_phieu_thu),
          );
        }
        $data[] = array(
          array('data' => 'Tổng tiền', 'colspan' => 2, 'class' => array('text-center', 'text-bold')),
          number_format($tong_tien_chua_giam),
          number_format($tong_tien),
          number_format($tien_bot),
          '',
          '',
          '',
          '',
        );
        break;
      case 'thule':
        $query = db_select('dt_phieu_thu_le', 'tl')->extend('TableSort');
        $query->fields('tl', array('id', 'ma_phieu_thu', 'ma_thu_quy', 'tong_tien_chua_giam', 'tong_tien', 'tien_bot', 'nguoi_nop', 'thanh_toan', 'thoi_gian', 'ma_nha_cung_cap'));
        $query->condition('tl.thoi_gian', $start_month, '>=');
        $query->condition('tl.thoi_gian', $end_month, '<=');
        $query->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = tl.ma_thu_quy');
        $query->addField('tq', 'ten_thu_quy');
        // $query->orderByHeader($header);
        $result = $query->orderBy('tl.thoi_gian', 'DESC')->execute();
        $tong_tien_chua_giam = 0;
        $tong_tien = 0;
        $tien_bot = 0;
        $data = array();
        $admin_perm = user_access('admin xoa quy');
        $admin_perm_edit = user_access('admin sua quy');
        $header = array(
          'Mã phiếu thu',
          'Số tiền trước giảm',
          'Số tiền thu',
          'Tiền bớt',
          'Thủ quỹ thu',
          'Thanh toán',
          'Ngày tháng',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $output = '<a href="../../quy_doanh_nghiep/phieu_thu_chi/xoa_phieu/' . $type . '/' . $rs->ma_phieu_thu . '" onclick="return check_xoa();">Xóa</a>';
          $tong_tien_chua_giam += $rs->tong_tien_chua_giam;
          $tong_tien += $rs->tong_tien;
          $tien_bot += $rs->tien_bot;
          $trang_thai = '';
          /*if ($rs->ma_nha_cung_cap == '') {
            $rs->thanh_toan ? $trang_thai = 'Đã chuyển tiền' : $trang_thai = 'Chưa chuyển tiền';
          } else {
            $trang_thai = 'Trừ công nợ NCC';
          }*/
          $rs->thanh_toan ? $trang_thai = 'Đã chuyển tiền' : $trang_thai = 'Chưa chuyển tiền';
          $data[] = array(
//            'ma_phieu_thu' => $rs->ma_phieu_thu,
//            'tong_tien_chua_giam' => number_format($rs->tong_tien_chua_giam),
//            'tong_tien' => number_format($rs->tong_tien),
//            'tien_bot' => number_format($rs->tien_bot),
//            'ma_thu_quy' => $rs->ten_thu_quy,
//            'thanh_toan' => $rs->thanh_toan ? 'Đã chuyển tiền' : 'Chưa chuyển tiền',
//            'thoi_gian' => date('d/m/Y', $rs->thoi_gian),
//            'thao_tac' => l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/' . $type . '/' . $rs->ma_phieu_thu)
            array('data' => $rs->ma_phieu_thu, 'class' => array('text-center')),
            array('data' => number_format($rs->tong_tien_chua_giam), 'class' => array('text-center')),
            array('data' => number_format($rs->tong_tien), 'class' => array('text-center')),
            array('data' => number_format($rs->tien_bot), 'class' => array('text-center')),
            array('data' => $rs->ten_thu_quy, 'class' => array('text-center')),
            array('data' => $trang_thai, 'class' => array('text-center')),
            array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('text-center')),
            ($admin_perm ? ($output . ' | ')
              : '') .
            ($admin_perm_edit
              ? (l('Sửa', 'quy_doanh_nghiep/thu_chi/sua_phieu/' . $type . '/' . $rs->ma_phieu_thu) . ' | ')
              : '') . l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/' . $type . '/' . $rs->ma_phieu_thu),
          );
        }
        $data[] = array(
          array('data' => 'Tổng tiền', 'class' => array('text-center', 'text-bold')),
          number_format($tong_tien_chua_giam),
          number_format($tong_tien),
          number_format($tien_bot),
          '',
          '',
          '',
          '',
        );
        break;
      case 'chile':
        $query = db_select('dt_phieu_chi_le', 'cl')->extend('TableSort');
        $query->fields('cl', array('id', 'ma_phieu_chi', 'ma_thu_quy', 'tong_tien', 'nguoi_nhan', 'thoi_gian'));
        $query->condition('cl.thoi_gian', $start_month, '>=');
        $query->condition('cl.thoi_gian', $end_month, '<=');
        $query->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = cl.ma_thu_quy');
        $query->addField('tq', 'ten_thu_quy');
        // $query->orderByHeader($header);
        $result = $query->orderBy('cl.thoi_gian', 'DESC')->execute();
        $tong_tien = 0;
        $admin_perm = user_access('admin xoa quy');
        $admin_perm_edit = user_access('admin dua');
        $data = array();
        $header = array(
          'Mã phiếu chi',
          'Thủ quỹ',
          'Số tiền',
          'Người nhận',
          'Ngày tháng',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $output = '<a href="../../quy_doanh_nghiep/phieu_thu_chi/xoa_phieu/' . $type . '/' . $rs->ma_phieu_chi . '" onclick="return check_xoa();">Xóa</a>';
          $tong_tien += $rs->tong_tien;
          $data[] = array(
//            'ma_phieu_chi' => $rs->ma_phieu_chi,
//            'ma_thu_quy' => $rs->ten_thu_quy,
//            'tong_tien' => number_format($rs->tong_tien),
//            'nguoi_nhan' => $rs->nguoi_nhan,
//            'thoi_gian' => date('d/m/Y', $rs->thoi_gian),
//            'thao_tac' => l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/' . $type . '/' . $rs->ma_phieu_chi)
            array('data' => $rs->ma_phieu_chi, 'class' => array('text-center')),
            array('data' => $rs->ten_thu_quy, 'class' => array('text-center')),
            array('data' => number_format($rs->tong_tien), 'class' => array('text-center')),
            array('data' => $rs->nguoi_nhan, 'class' => array('text-center')),
            array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('text-center')),
            ($admin_perm ? ($output . ' | ')
              : '') .
            ($admin_perm_edit
              ? (l('Sửa', 'quy_doanh_nghiep/thu_chi/sua_phieu/' . $type . '/' . $rs->ma_phieu_chi) . ' | ')
              : '') . l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/' . $type . '/' . $rs->ma_phieu_chi),
          );
        }
        $data[] = array(
          array('data' => 'Tổng tiền', 'colspan' => 2, 'class' => array('text-center', 'text-bold')),
          number_format($tong_tien),
          '',
          '',
          '',
        );
        break;
      case 'chivattu':
        $query = db_select('dt_phieu_chi_vat_tu', 'cvt')->extend('TableSort');
        $query->fields('cvt', array('id', 'ma_phieu_chi', 'ma_nha_cung_cap', 'ma_thu_quy', 'tong_tien', 'nguoi_nhan', 'thoi_gian'));
        $query->condition('cvt.thoi_gian', $start_month, '>=');
        $query->condition('cvt.thoi_gian', $end_month, '<=');
        $query->leftJoin('dt_nha_cung_cap', 'ncc', 'ncc.ma_nha_cung_cap = cvt.ma_nha_cung_cap');
        $query->addField('ncc', 'ten_nha_cung_cap');
        $query->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = cvt.ma_thu_quy');
        $query->addField('tq', 'ten_thu_quy');
        $tong_tien = 0;
        // $query->orderByHeader($header);
        $result = $query->orderBy('cvt.thoi_gian', 'DESC')->execute();
        $data = array();
        $admin_perm = user_access('admin xoa quy');
        $admin_perm_edit = user_access('admin sua quy');
        $header = array(
          'Mã phiếu chi',
          'Nhà cung cấp',
          'Thủ quỹ',
          'Số tiền',
          'Người nhận',
          'Ngày tháng',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $output = '<a href="../../quy_doanh_nghiep/phieu_thu_chi/xoa_phieu/' . $type . '/' . $rs->ma_phieu_chi . '" onclick="return check_xoa();">Xóa</a>';
          $tong_tien += $rs->tong_tien;
          $data[] = array(
//            'ma_phieu_chi' => $rs->ma_phieu_chi,
//            'ma_nha_cung_cap' => $rs->ten_nha_cung_cap,
//            'ma_thu_quy' => $rs->ten_thu_quy,
//            'tong_tien' => number_format($rs->tong_tien),
//            'nguoi_nhan' => $rs->nguoi_nhan,
//            'thoi_gian' => date('d/m/Y', $rs->thoi_gian),
//            'thao_tac' => l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/' . $type . '/' . $rs->ma_phieu_chi)
            array('data' => $rs->ma_phieu_chi, 'class' => array('text-center')),
            array('data' => $rs->ten_nha_cung_cap, 'class' => array('text-center')),
            array('data' => $rs->ten_thu_quy, 'class' => array('text-center')),
            array('data' => number_format($rs->tong_tien), 'class' => array('text-center')),
            array('data' => $rs->nguoi_nhan, 'class' => array('text-center')),
            array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('text-center')),
            ($admin_perm ? ($output . ' | ')
              : '') .
            ($admin_perm_edit
              ? (l('Sửa', 'quy_doanh_nghiep/thu_chi/sua_phieu/' . $type . '/' . $rs->ma_phieu_chi) . ' | ')
              : '') . l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/' . $type . '/' . $rs->ma_phieu_chi),
          );
        }
        $data[] = array(
          array('data' => 'Tổng tiền', 'colspan' => 3, 'class' => array('text-center', 'text-bold')),
          number_format($tong_tien),
          '',
          '',
          '',
        );
        break;
      case 'chuyentien':
        $query = db_select('dt_giao_tien', 'gt')->extend('TableSort');
        $query->fields('gt', array('id', 'ma_phieu_chuyen', 'ma_thu_quy_chi', 'ma_thu_quy_nhan', 'tong_tien', 'thoi_gian'));
        $query->condition('gt.thoi_gian', $start_month, '>=');
        $query->condition('gt.thoi_gian', $end_month, '<=');
        $query->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = gt.ma_thu_quy_chi');
        $query->addField('tq', 'ten_thu_quy', 'ten_thu_quy_chi');
        $query->leftJoin('dt_thu_quy', 'tq1', 'tq1.ma_thu_quy = gt.ma_thu_quy_nhan');
        $query->addField('tq1', 'ten_thu_quy', 'ten_thu_quy_nhan');
        // $query->orderByHeader($header);
        $tong_tien = 0;
        $result = $query->orderBy('gt.thoi_gian', 'DESC')->execute();
        $data = array();
        $admin_perm = user_access('admin xoa quy');
        $admin_perm_edit = user_access('admin sua quy');
        $header = array(
          'Mã phiếu chuyển',
          'Thủ quỹ chi',
          'Thủ quỹ nhận',
          'Số tiền',
          'Ngày tháng',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $output = '<a href="../../quy_doanh_nghiep/phieu_thu_chi/xoa_phieu/' . $type . '/' . $rs->ma_phieu_chuyen . '" onclick="return check_xoa();">Xóa</a>';
          $tong_tien += $rs->tong_tien;
          $data[] = array(
//            'ma_phieu_chuyen' => $rs->ma_phieu_chuyen,
//            'ma_thu_quy_chi' => $rs->ten_thu_quy_chi,
//            'ma_thu_quy_nhan' => $rs->ten_thu_quy_nhan,
//            'tong_tien' => number_format($rs->tong_tien),
//            'thoi_gian' => date('d/m/Y', $rs->thoi_gian),
//            'thao_tac' => l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/' . $type . '/' . $rs->ma_phieu_chuyen)
            array('data' => $rs->ma_phieu_chuyen, 'class' => array('text-center')),
            array('data' => $rs->ten_thu_quy_chi, 'class' => array('text-center')),
            array('data' => $rs->ten_thu_quy_nhan, 'class' => array('text-center')),
            array('data' => number_format($rs->tong_tien), 'class' => array('text-center')),
            array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('text-center')),
            ($admin_perm ? ($output . ' | ')
              : '') .
            ($admin_perm_edit
              ? (l('Sửa', 'quy_doanh_nghiep/thu_chi/sua_phieu/' . $type . '/' . $rs->ma_phieu_chuyen) . ' | ')
              : '') . l('Chi tiết', '../quy_doanh_nghiep/phieu_thu_chi/in_phieu/' . $type . '/' . $rs->ma_phieu_chuyen),
          );
        }
        $data[] = array(
          array('data' => 'Tổng tiền', 'colspan' => 3, 'class' => array('text-center', 'text-bold')),
          number_format($tong_tien),
          '',
          '',
        );
        break;
      case 'giaotienbanhang':
        $query = db_select('dt_phieu_chuyen_tien_hang', 'pcth')->extend('TableSort');
        $query->fields('pcth', array('id', 'ma_phieu_chuyen', 'ma_thu_quy_nhan', 'ma_nhan_vien_ban_hang', 'tong_tien', 'thoi_gian'));
        $query->condition('pcth.thoi_gian', $start_month, '>=');
        $query->condition('pcth.thoi_gian', $end_month, '<=');
        $query->leftJoin('dt_thu_quy', 'tp1', 'tp1.ma_thu_quy = pcth.ma_thu_quy_nhan');
        $query->addField('tp1', 'ten_thu_quy', 'ten_thu_quy_nhan');
        $query->leftJoin('dt_thu_quy', 'tp2', 'tp2.ma_thu_quy = pcth.ma_nhan_vien_ban_hang');
        $query->addField('tp2', 'ten_thu_quy', 'ten_nhan_vien_ban_hang');
        // $query->orderByHeader($header);
        $result = $query->orderBy('pcth.thoi_gian', 'DESC')->execute();
        $data = array();
        $admin_perm = user_access('admin xoa quy');
        $tong_tien = 0;
        $header = array(
          'Mã phiếu',
          'Thủ quỹ nhận',
          'Nhân viên bán hàng',
          'Số tiền',
          'Thời gian',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $output = '<a href="../../quy_doanh_nghiep/phieu_thu_chi/xoa_phieu/' . $type . '/' . $rs->ma_phieu_chuyen . '" onclick="return check_xoa();">Xóa</a>';
          $tong_tien += $rs->tong_tien;
          $data[] = array(
//            'ma_phieu_chuyen' => $rs->ma_phieu_chuyen,
//            'ma_thu_quy_nhan' => $rs->ten_thu_quy_nhan,
//            'ma_nhan_vien_ban_hang' => $rs->ten_nhan_vien_ban_hang,
//            'tong_tien' => number_format($rs->tong_tien),
//            'thoi_gian' => date('d/m/Y', $rs->thoi_gian),
//            'thao_tac' => l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/' . $type . '/' . $rs->ma_phieu_chuyen)
            array('data' => $rs->ma_phieu_chuyen, 'class' => array('text-center')),
            array('data' => $rs->ten_thu_quy_nhan, 'class' => array('text-center')),
            array('data' => $rs->ten_nhan_vien_ban_hang, 'class' => array('text-center')),
            array('data' => number_format($rs->tong_tien), 'class' => array('text-center')),
            array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('text-center')),
            ($admin_perm ? ($output . ' | ')
              : '') . l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/' . $type . '/' . $rs->ma_phieu_chuyen),
          );
        }
        $data[] = array(
          array('data' => 'Tổng tiền', 'colspan' => 3, 'class' => array('text-center', 'text-bold')),
          number_format($tong_tien),
          '',
          '',
        );
        break;
    }
    return array('header' => $header, 'rows' => $data);
  }

  function get_phieu_chuyen_tien_ban_hang($type, $header) {
    $query = db_select('dt_phieu_chuyen_tien_hang', 'pcth')->extend('TableSort');
    $query->fields('pcth', array('id', 'ma_phieu_chuyen', 'ma_thu_quy_nhan', 'ma_nhan_vien_ban_hang', 'tong_tien', 'thoi_gian'));
    $query->leftJoin('dt_thu_quy', 'tp1', 'tp1.ma_thu_quy = pcth.ma_thu_quy_nhan');
    $query->addField('tp1', 'ten_thu_quy', 'ten_thu_quy_nhan');
    $query->leftJoin('dt_thu_quy', 'tp2', 'tp2.ma_thu_quy = pcth.ma_nhan_vien_ban_hang');
    $query->addField('tp2', 'ten_thu_quy', 'ten_nhan_vien_ban_hang');
    $query->orderByHeader($header);
    $result = $query->orderBy('pcth.thoi_gian', 'DESC')->limit(20)->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
        'ma_phieu_chuyen' => $rs->ma_phieu_chuyen,
        'ma_thu_quy_nhan' => $rs->ten_thu_quy_nhan,
        'ma_nhan_vien_ban_hang' => $rs->ten_nhan_vien_ban_hang,
        'tong_tien' => number_format($rs->tong_tien),
        'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
        'thao_tac' => l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/' . $type . '/' . $rs->ma_phieu_chuyen)
      );
    }
    return $data;
  }

  function get_phieu_thu_chi_theo_ma($type, $ma_phieu) {
    switch ($type) {
      case 'thusanpham':
        $query = db_select('dt_phieu_thu_thanh_pham', 'ttp');
        $query->fields('ttp', array('id', 'ma_phieu_thu', 'ma_loai_tien', 'ma_khach_hang', 'ma_thu_quy', 'tong_tien', 'tong_tien_chua_giam', 'nguoi_nop', 'thanh_toan', 'thoi_gian', 'ghi_chu'));
        $query->addField('ttp', 'ma_phieu_thu', 'ma_phieu');
        $query->leftJoin('dt_khach_hang', 'kd', 'kd.ma_khach_hang = ttp.ma_khach_hang');
        $query->addField('kd', 'ten_khach_hang');
        $query->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = ttp.ma_thu_quy');
        $query->addField('tq', 'ten_thu_quy');
        $query->condition('ttp.ma_phieu_thu', $ma_phieu);
        break;
      case 'thule':
        $query = db_select('dt_phieu_thu_le', 'tl');
        $query->fields('tl', array('id', 'ma_phieu_thu', 'ma_thu_quy', 'ma_nha_cung_cap', 'tong_tien_chua_giam', 'tong_tien', 'nguoi_nop', 'thanh_toan', 'thoi_gian', 'ghi_chu'));
        $query->addField('tl', 'ma_phieu_thu', 'ma_phieu');
        $query->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = tl.ma_thu_quy');
        $query->addField('tq', 'ten_thu_quy');
        $query->leftJoin('dt_nha_cung_cap', 'ncc', 'ncc.ma_nha_cung_cap = tl.ma_nha_cung_cap');
        $query->addField('ncc', 'ten_nha_cung_cap');
        $query->condition('tl.ma_phieu_thu', $ma_phieu);
        break;
      case 'chile':
        $query = db_select('dt_phieu_chi_le', 'cl');
        $query->fields('cl', array('id', 'ma_phieu_chi', 'ma_thu_quy', 'tong_tien', 'nguoi_nhan', 'thoi_gian', 'ghi_chu', 'ma_khach_hang'));
        $query->addField('cl', 'ma_phieu_chi', 'ma_phieu');
        $query->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = cl.ma_thu_quy');
        $query->addField('tq', 'ten_thu_quy');
        $query->leftJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = cl.ma_khach_hang');
        $query->addField('kh', 'ten_khach_hang');
        $query->condition('cl.ma_phieu_chi', $ma_phieu);
        break;
      case 'chivattu':
        $query = db_select('dt_phieu_chi_vat_tu', 'cvt');
        $query->fields('cvt', array('id', 'ma_phieu_chi', 'ma_nha_cung_cap', 'ma_thu_quy', 'tong_tien', 'nguoi_nhan', 'thoi_gian', 'ghi_chu'));
        $query->addField('cvt', 'ma_phieu_chi', 'ma_phieu');
        $query->leftJoin('dt_nha_cung_cap', 'ncc', 'ncc.ma_nha_cung_cap = cvt.ma_nha_cung_cap');
        $query->addField('ncc', 'ten_nha_cung_cap');
        $query->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = cvt.ma_thu_quy');
        $query->addField('tq', 'ten_thu_quy');
        $query->condition('cvt.ma_phieu_chi', $ma_phieu);
        break;
      case 'chuyentien':
        $query = db_select('dt_giao_tien', 'gt');
        $query->fields('gt', array('id', 'ma_phieu_chuyen', 'ma_thu_quy_chi', 'ma_thu_quy_nhan', 'tong_tien', 'thoi_gian', 'ghi_chu'));
        $query->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = gt.ma_thu_quy_chi');
        $query->addField('gt', 'ma_phieu_chuyen', 'ma_phieu');
        $query->addField('tq', 'ten_thu_quy', 'ten_thu_quy_chi');
        $query->leftJoin('dt_thu_quy', 'tq1', 'tq1.ma_thu_quy = gt.ma_thu_quy_nhan');
        $query->addField('tq1', 'ten_thu_quy', 'ten_thu_quy_nhan');
        $query->condition('gt.ma_phieu_chuyen', $ma_phieu);
        break;
      case 'giaotienbanhang':
        $query = db_select('dt_phieu_chuyen_tien_hang', 'pcth')->extend('TableSort');
        $query->fields('pcth', array('id', 'ma_phieu_chuyen', 'ma_thu_quy_nhan', 'ma_nhan_vien_ban_hang', 'tong_tien', 'thoi_gian', 'ghi_chu'));
        $query->leftJoin('dt_thu_quy', 'tp1', 'tp1.ma_thu_quy = pcth.ma_thu_quy_nhan');
        $query->addField('tp1', 'ten_thu_quy', 'ten_thu_quy_nhan');
        $query->addField('pcth', 'ma_phieu_chuyen', 'ma_phieu');
        $query->leftJoin('dt_thu_quy', 'tp2', 'tp2.ma_thu_quy = pcth.ma_nhan_vien_ban_hang');
        $query->addField('tp2', 'ten_thu_quy', 'ten_nhan_vien_ban_hang');
        $query->condition('pcth.ma_phieu_chuyen', $ma_phieu);
        break;
      case 'trichquy':
        $query = db_select('dt_phieu_trich_quy', 'trq');
        $query->fields('trq', array('id', 'ma_phieu_trich', 'ma_thu_quy', 'tong_tien', 'nguoi_nhan', 'thoi_gian', 'ghi_chu'));
        $query->addField('trq', 'ma_phieu_trich', 'ma_phieu');
        $query->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = trq.ma_thu_quy');
        $query->addField('tq', 'ten_thu_quy');
        $query->condition('trq.ma_phieu_trich', $ma_phieu);
        break;
    }
    $result = $query->execute();
    $rs = $result->fetchObject();
    return $rs;
  }

  function xoa_phieu_thu_chi($type, $ma_phieu, $phieu) {
    switch ($type) {
      case 'thusanpham':
        update_tien_du('-', $phieu->tong_tien, $phieu->ma_thu_quy);
        module_load_include('inc', 'khach_hang', 'model/khach_hang');
        update_tien_no_khach_hang('+', $phieu->tong_tien_chua_giam, $phieu->ma_khach_hang);
        db_delete('dt_phieu_thu_thanh_pham')
          ->condition('ma_phieu_thu', $ma_phieu)
          ->execute();
        break;
      case 'thule':
        update_tien_du('-', $phieu->tong_tien, $phieu->ma_thu_quy);
        module_load_include('inc', 'nha_cung_cap', 'model/nha_cung_cap');
        update_tien_no_nha_cung_cap('-', $phieu->tong_tien_chua_giam, $phieu->ma_nha_cung_cap);
        db_delete('dt_phieu_thu_le')
          ->condition('ma_phieu_thu', $ma_phieu)
          ->execute();
        break;
      case 'chile':
        update_tien_du('+', $phieu->tong_tien, $phieu->ma_thu_quy);
        db_delete('dt_phieu_chi_le')
          ->condition('ma_phieu_chi', $ma_phieu)
          ->execute();
        break;
      case 'chivattu':
        update_tien_du('+', $phieu->tong_tien, $phieu->ma_thu_quy);
        module_load_include('inc', 'nha_cung_cap', 'model/nha_cung_cap');
        update_tien_no_nha_cung_cap('+', $phieu->tong_tien, $phieu->ma_nha_cung_cap);
        db_delete('dt_phieu_chi_vat_tu')
          ->condition('ma_phieu_chi', $ma_phieu)
          ->execute();
        break;
      case 'chuyentien':
        update_tien_du('+', $phieu->tong_tien, $phieu->ma_thu_quy_chi);
        update_tien_du('-', $phieu->tong_tien, $phieu->ma_thu_quy_nhan);
        db_delete('dt_giao_tien')
          ->condition('ma_phieu_chuyen', $ma_phieu)
          ->execute();
        break;
      case 'giaotienbanhang':
        update_tien_du('-', $phieu->tong_tien, $phieu->ma_thu_quy_nhan);
        update_tien_du('+', $phieu->tong_tien, $phieu->ma_nhan_vien_ban_hang);
        $phieu_thu_chi_chi_tiet = get_phieu_thu_chi_chi_tiet_theo_ma($ma_phieu, false);
        foreach ($phieu_thu_chi_chi_tiet as $phieu_thu_chi)
        {
          db_update('dt_phieu_thu_thanh_pham')
            ->fields(array(
                          'ma_phieu_chuyen' => '',
                          'thanh_toan' => 0,
                     ))
            ->condition('ma_phieu_thu', $phieu_thu_chi->ma_phieu_thu)
            ->execute();
          db_update('dt_phieu_thu_le')
            ->fields(array(
                          'ma_phieu_chuyen' => '',
                          'thanh_toan' => 0,
                     ))
            ->condition('ma_phieu_thu', $phieu_thu_chi->ma_phieu_thu)
            ->execute();
        }
        db_delete('dt_phieu_chuyen_tien_hang')
          ->condition('ma_phieu_chuyen', $ma_phieu)
          ->execute();
        break;
      case 'trichquy':
        update_tien_du('+', $phieu->tong_tien, $phieu->ma_thu_quy);
        db_delete('dt_phieu_trich_quy')
          ->condition('ma_phieu_trich', $ma_phieu)
          ->execute();
        break;
    }
  }

  function get_phieu_thu_chi_chi_tiet_theo_ma($ma_phieu, $get_table = true) {
    $query_n = db_select('dt_phieu_thu_thanh_pham', 'n');
    $query_n->condition('ma_phieu_chuyen', $ma_phieu);
    $query_n->fields('n', array('id', 'ma_phieu_thu', 'tong_tien', 'nguoi_nop', 'thoi_gian', 'ma_loai_tien'));
    $query_n->addExpression("'Phiếu thu tiền hàng'", 'type');
    $query_n->leftJoin('dt_khach_hang', 'kd', 'kd.ma_khach_hang = n.ma_khach_hang');
    $query_n->addField('kd', 'ten_khach_hang', 'khach_hang');
    $query_x = db_select('dt_phieu_thu_le', 'x');
    $query_x->condition('ma_phieu_chuyen', $ma_phieu);
    $query_x->fields('x', array('id', 'ma_phieu_thu', 'tong_tien', 'nguoi_nop', 'thoi_gian', 'ma_loai_tien'));
    $query_x->addExpression("'Phiếu thu lẻ'", 'type');
    $query_x->addField('x', 'nguoi_nop', 'khach_hang');
    $query_n->union($query_x, 'UNION ALL');
    $query = db_select($query_n, 'pt')->extend('PagerDefault');
    $query->fields('pt', array('id', 'ma_phieu_thu', 'tong_tien', 'nguoi_nop', 'thoi_gian', 'ma_loai_tien', 'type', 'khach_hang'));
    $result = $query->orderBy('ma_loai_tien', 'DESC')->execute();
    $data = array();
    $loai_tien = get_option_loai_tien();
    if ($get_table) {
      $data['header'] = array(
        'Mã phiếu thu',
        'Loại phiếu',
        'Khách hàng',
        'Số tiền',
        'Loại tiền',
        'Thời gian',
      );
      while ($rs = $result->fetchObject()) {
        $data['rows'][] = array(
          array('data' => $rs->ma_phieu_thu, 'class' => array('')),
          array('data' => $rs->type, 'class' => array('')),
          array('data' => $rs->khach_hang, 'class' => array('')),
          array('data' => number_format($rs->tong_tien), 'class' => array('')),
          array('data' => $loai_tien[$rs->ma_loai_tien], 'class' => array('')),
          array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array(''))
        );
      }
    }
    else {
      while ($rs = $result->fetchObject()) {
        $data[] = $rs;
      }
    }
    return $data;
  }

  function update_tien_du($sign, $tong_tien, $ma_thu_quy) {
    db_update('dt_thu_quy')
      ->expression('tong_tien', 'tong_tien ' . $sign . ' :tong_tien', array(':tong_tien' => $tong_tien))
      ->condition('ma_thu_quy', $ma_thu_quy)
      ->execute();
  }

  function update_tien_bot_thu_tp($ma_phieu_thu) {
    db_update('dt_phieu_thu_thanh_pham')
      ->expression('tien_bot', 'tong_tien_chua_giam - tong_tien')
      ->condition('ma_phieu_thu', $ma_phieu_thu)
      ->execute();
  }

  function update_tien_bot_thu_le($ma_phieu_thu) {
    db_update('dt_phieu_thu_le')
      ->expression('tien_bot', 'tong_tien_chua_giam - tong_tien')
      ->condition('ma_phieu_thu', $ma_phieu_thu)
      ->execute();
  }

  function get_danh_sach_thu_quy($header) {
    $query = db_select('dt_thu_quy', 'tq')->extend('PagerDefault')->extend('TableSort');
    $query->fields('tq', array('id', 'ma_thu_quy', 'ten_thu_quy', 'loai', 'tong_tien'));
    $query->orderByHeader($header);
    $result = $query->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
        'ten_thu_quy' => $rs->ten_thu_quy,
        'loai' => $rs->loai ? 'Nhân viên bán hàng' : 'Thủ quỹ',
        'tong_tien' => number_format($rs->tong_tien),
      );
    }
    return $data;
  }

  function get_thong_ke_thu_chi_ngay($start_date, $end_date, $start_month, $end_month) {
    $query = db_select('dt_thu_quy', 'tq');
    $query->fields('tq', array('id', 'ma_thu_quy', 'ten_thu_quy', 'thoi_gian'));
    $query->addExpression('(SELECT SUM(n0.tong_tien) FROM {dt_giao_tien} AS n0 WHERE tq.ma_thu_quy = n0.ma_thu_quy_nhan AND n0.thoi_gian < :n0_start_month)', 'nhan_den_thang', array(':n0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(tsp0.tong_tien) FROM {dt_phieu_thu_thanh_pham} AS tsp0 WHERE tq.ma_thu_quy = tsp0.ma_thu_quy AND tsp0.thoi_gian < :tsp0_start_month)', 'tsp0_den_thang', array(':tsp0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(tl0.tong_tien) FROM {dt_phieu_thu_le} AS tl0 WHERE tq.ma_thu_quy = tl0.ma_thu_quy AND tl0.thoi_gian < :tl0_start_month)', 'tl0_den_thang', array(':tl0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(cvt0.tong_tien) FROM {dt_phieu_chi_vat_tu} AS cvt0 WHERE tq.ma_thu_quy = cvt0.ma_thu_quy AND cvt0.thoi_gian < :cvt0_start_month)', 'cvt0_den_thang', array(':cvt0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(cl0.tong_tien) FROM {dt_phieu_chi_le} AS cl0 WHERE tq.ma_thu_quy = cl0.ma_thu_quy AND cl0.thoi_gian < :cl0_start_month)', 'cl0_den_thang', array(':cl0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(c0.tong_tien) FROM {dt_giao_tien} AS c0 WHERE tq.ma_thu_quy = c0.ma_thu_quy_chi AND c0.thoi_gian < :c0_start_month)', 'chi_den_thang', array(':c0_start_month' => $start_month));
    $query->addExpression('(SELECT d.tong_tien FROM {dt_thu_quy_du_lieu} AS d WHERE tq.ma_thu_quy = d.ma_thu_quy AND d.thoi_gian < :d_start_month)', 'du', array(':d_start_month' => $start_month));
    $query->addExpression('(SELECT tr0.tong_tien FROM {dt_phieu_trich_quy} AS tr0 WHERE tq.ma_thu_quy = tr0.ma_thu_quy AND tr0.thoi_gian < :tr0_start_month)', 'trich_den_thang', array(':tr0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(bht.tong_tien) FROM {dt_phieu_chuyen_tien_hang} AS bht WHERE tq.ma_thu_quy = bht.ma_thu_quy_nhan AND bht.thoi_gian < :bht_start_month)', 'thu_ban_hang_den_thang', array(':bht_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(bhc.tong_tien) FROM {dt_phieu_chuyen_tien_hang} AS bhc WHERE tq.ma_thu_quy = bhc.ma_nhan_vien_ban_hang AND bhc.thoi_gian < :bhc_start_month)', 'chi_ban_hang_den_thang', array(':bhc_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(n1.tong_tien) FROM {dt_giao_tien} AS n1 WHERE tq.ma_thu_quy = n1.ma_thu_quy_nhan AND n1.thoi_gian >= :n1_start_month AND n1.thoi_gian <= :n1_end_month)', 'nhap_thang', array(':n1_start_month' => $start_month, ':n1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(tsp1.tong_tien) FROM {dt_phieu_thu_thanh_pham} AS tsp1 WHERE tq.ma_thu_quy = tsp1.ma_thu_quy AND tsp1.thoi_gian >= :tsp1_start_month AND tsp1.thoi_gian <= :tsp1_end_month)', 'tsp1_thang', array(':tsp1_start_month' => $start_month, ':tsp1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(tl1.tong_tien) FROM {dt_phieu_thu_le} AS tl1 WHERE tq.ma_thu_quy = tl1.ma_thu_quy AND tl1.thoi_gian >= :tl1_start_month AND tl1.thoi_gian <= :tl1_end_month)', 'tl1_thang', array(':tl1_start_month' => $start_month, ':tl1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(cvt1.tong_tien) FROM {dt_phieu_chi_vat_tu} AS cvt1 WHERE tq.ma_thu_quy = cvt1.ma_thu_quy AND cvt1.thoi_gian >= :cvt1_start_month AND cvt1.thoi_gian <= :cvt1_end_month)', 'cvt1_thang', array(':cvt1_start_month' => $start_month, ':cvt1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(cl1.tong_tien) FROM {dt_phieu_chi_le} AS cl1 WHERE tq.ma_thu_quy = cl1.ma_thu_quy AND cl1.thoi_gian >= :cl1_start_month AND cl1.thoi_gian <= :cl1_end_month)', 'cl1_thang', array(':cl1_start_month' => $start_month, ':cl1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(c1.tong_tien) FROM {dt_giao_tien} AS c1 WHERE tq.ma_thu_quy = c1.ma_thu_quy_chi AND c1.thoi_gian >= :c1_start_month AND c1.thoi_gian <= :c1_end_month)', 'chi_thang', array(':c1_start_month' => $start_month, ':c1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(bhtt.tong_tien) FROM {dt_phieu_chuyen_tien_hang} AS bhtt WHERE tq.ma_thu_quy = bhtt.ma_thu_quy_nhan AND bhtt.thoi_gian >= :bhtt_start_month AND bhtt.thoi_gian <= :bhtt_end_month)', 'thu_ban_hang_thang', array(':bhtt_start_month' => $start_month, ':bhtt_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(bhct.tong_tien) FROM {dt_phieu_chuyen_tien_hang} AS bhct WHERE tq.ma_thu_quy = bhct.ma_nhan_vien_ban_hang AND bhct.thoi_gian >= :bhct_start_month AND bhct.thoi_gian <= :bhct_end_month)', 'chi_ban_hang_thang', array(':bhct_start_month' => $start_month, ':bhct_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(ttpt.tien_bot) FROM {dt_phieu_thu_thanh_pham} AS ttpt WHERE tq.ma_thu_quy = ttpt.ma_thu_quy AND ttpt.thoi_gian >= :ttpt_start_month AND ttpt.thoi_gian <= :ttpt_end_month)', 'tien_bot_sp_thang', array(':ttpt_start_month' => $start_month, ':ttpt_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(tlt.tien_bot) FROM {dt_phieu_thu_le} AS tlt WHERE tq.ma_thu_quy = tlt.ma_thu_quy AND tlt.thoi_gian >= :tlt_start_month AND tlt.thoi_gian <= :tlt_end_month)', 'tien_bot_le_thang', array(':tlt_start_month' => $start_month, ':tlt_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(bh1.tien_bot) FROM {dt_ban_hang} AS bh1 WHERE bh1.uid = tq.ma_thu_quy AND bh1.thoi_gian >= :bh1_start_month AND bh1.thoi_gian <= :bh1_end_month)', 'bh_thang', array(':bh1_start_month' => $start_month, ':bh1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(tr1.tong_tien) FROM {dt_phieu_trich_quy} AS tr1 WHERE tq.ma_thu_quy = tr1.ma_thu_quy AND tr1.thoi_gian >= :tr1_start_month AND tr1.thoi_gian <= :tr1_end_month)', 'trich_thang', array(':tr1_start_month' => $start_month, ':tr1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(n2.tong_tien) FROM {dt_giao_tien} AS n2 WHERE tq.ma_thu_quy = n2.ma_thu_quy_nhan AND n2.thoi_gian >= :n2_start_date AND n2.thoi_gian <= :n2_end_date)', 'nhap_ngay', array(':n2_start_date' => $start_date, ':n2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(tsp2.tong_tien) FROM {dt_phieu_thu_thanh_pham} AS tsp2 WHERE tq.ma_thu_quy = tsp2.ma_thu_quy AND tsp2.thoi_gian >= :tsp2_start_date AND tsp2.thoi_gian <= :tsp2_end_date)', 'tsp2_ngay', array(':tsp2_start_date' => $start_date, ':tsp2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(tl2.tong_tien) FROM {dt_phieu_thu_le} AS tl2 WHERE tq.ma_thu_quy = tl2.ma_thu_quy AND tl2.thoi_gian >= :tl2_start_date AND tl2.thoi_gian <= :tl2_end_date)', 'tl2_ngay', array(':tl2_start_date' => $start_date, ':tl2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(cvt2.tong_tien) FROM {dt_phieu_chi_vat_tu} AS cvt2 WHERE tq.ma_thu_quy = cvt2.ma_thu_quy AND cvt2.thoi_gian >= :cvt2_start_date AND cvt2.thoi_gian <= :cvt2_end_date)', 'cvt2_ngay', array(':cvt2_start_date' => $start_date, ':cvt2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(cl2.tong_tien) FROM {dt_phieu_chi_le} AS cl2 WHERE tq.ma_thu_quy = cl2.ma_thu_quy AND cl2.thoi_gian >= :cl2_start_date AND cl2.thoi_gian <= :cl2_end_date)', 'cl2_ngay', array(':cl2_start_date' => $start_date, ':cl2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(c2.tong_tien) FROM {dt_giao_tien} AS c2 WHERE tq.ma_thu_quy = c2.ma_thu_quy_chi AND c2.thoi_gian >= :c2_start_date AND c2.thoi_gian <= :c2_end_date)', 'chi_ngay', array(':c2_start_date' => $start_date, ':c2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(bhtn.tong_tien) FROM {dt_phieu_chuyen_tien_hang} AS bhtn WHERE tq.ma_thu_quy = bhtn.ma_thu_quy_nhan AND bhtn.thoi_gian >= :bhtn_start_date AND bhtn.thoi_gian <= :bhtn_end_date)', 'thu_ban_hang_ngay', array(':bhtn_start_date' => $start_date, ':bhtn_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(bhcn.tong_tien) FROM {dt_phieu_chuyen_tien_hang} AS bhcn WHERE tq.ma_thu_quy = bhcn.ma_nhan_vien_ban_hang AND bhcn.thoi_gian >= :bhcn_start_date AND bhcn.thoi_gian <= :bhcn_end_date)', 'chi_ban_hang_ngay', array(':bhcn_start_date' => $start_date, ':bhcn_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(ttpn.tien_bot) FROM {dt_phieu_thu_thanh_pham} AS ttpn WHERE tq.ma_thu_quy = ttpn.ma_thu_quy AND ttpn.thoi_gian >= :ttpn_start_date AND ttpn.thoi_gian <= :ttpn_end_date)', 'tien_bot_sp_ngay', array(':ttpn_start_date' => $start_date, ':ttpn_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(tln.tien_bot) FROM {dt_phieu_thu_le} AS tln WHERE tq.ma_thu_quy = tln.ma_thu_quy AND tln.thoi_gian >= :tln_start_date AND tln.thoi_gian <= :tln_end_date)', 'tien_bot_le_ngay', array(':tln_start_date' => $start_date, ':tln_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(bh2.tien_bot) FROM {dt_ban_hang} AS bh2 WHERE bh2.uid = tq.ma_thu_quy AND bh2.thoi_gian >= :bh2_start_date AND bh2.thoi_gian <= :bh2_end_date)', 'bh_ngay', array(':bh2_start_date' => $start_date, ':bh2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(tr2.tong_tien) FROM {dt_phieu_trich_quy} AS tr2 WHERE tq.ma_thu_quy = tr2.ma_thu_quy AND tr2.thoi_gian >= :tr2_start_date AND tr2.thoi_gian <= :tr2_end_date)', 'trich_ngay', array(':tr2_start_date' => $start_date, ':tr2_end_date' => $end_date));
    $result = $query->orderBy('tq.ma_thu_quy')->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $ma = $rs->ma_thu_quy;
      if ($ma == 'TQ_001') {
        if (user_access('xem quy ban giam doc')) {
          if ($ma[0] == 'T') {
            $type = '_';
          } else {
            $type = '_bh';
          }
          $data[] = array(
            //l($rs->ten_vat_tu . ' (' . $rs->ma_vat_tu . ')', 'vat_tu/thong_ke_vat_tu/chi_tiet_vat_tu/den_ngay/' . $rs->ma_vat_tu),
            array('data' => $rs->ten_thu_quy, 'class' => array('text-center')),
            array('data' => number_format($rs->thu_ban_hang_den_thang + $rs->nhan_den_thang + $rs->tsp0_den_thang + $rs->tl0_den_thang - $rs->chi_ban_hang_den_thang - $rs->cvt0_den_thang - $rs->cl0_den_thang - $rs->chi_den_thang + $rs->du - $rs->trich_den_thang), 'class' => array('text-center')),
            $rs->thu_ban_hang_thang + $rs->nhap_thang + $rs->tsp1_thang + $rs->tl1_thang
              ? l(number_format($rs->thu_ban_hang_thang + $rs->nhap_thang + $rs->tsp1_thang + $rs->tl1_thang), 'quy_doanh_nghiep/thong_ke_thu_chi/chi_tiet_phieu/thu' . $type . '/den_ngay/' . $rs->ma_thu_quy)
              : '',
            $rs->chi_ban_hang_thang + $rs->cvt1_thang + $rs->cl1_thang + $rs->chi_thang + $rs->trich_thang
              ? l(number_format($rs->chi_ban_hang_thang + $rs->cvt1_thang + $rs->cl1_thang + $rs->chi_thang + $rs->trich_thang), 'quy_doanh_nghiep/thong_ke_thu_chi/chi_tiet_phieu/chi' . $type . '/den_ngay/' . $rs->ma_thu_quy)
              : '',
            $rs->tien_bot_sp_thang + $rs->tien_bot_le_thang + $rs->bh_thang
              ? l(number_format($rs->tien_bot_sp_thang + $rs->tien_bot_le_thang + $rs->bh_thang), 'quy_doanh_nghiep/thong_ke_thu_chi/chi_tiet_phieu/bot/den_ngay/' . $rs->ma_thu_quy)
              : '',
            $rs->thu_ban_hang_ngay + $rs->nhap_ngay + $rs->tsp2_ngay + $rs->tl2_ngay
              ? l(number_format($rs->thu_ban_hang_ngay + $rs->nhap_ngay + $rs->tsp2_ngay + $rs->tl2_ngay), 'quy_doanh_nghiep/thong_ke_thu_chi/chi_tiet_phieu/thu' . $type . '/trong_ngay/' . $rs->ma_thu_quy)
              : '',
            $rs->chi_ban_hang_ngay + $rs->cvt2_ngay + $rs->cl2_ngay + $rs->chi_ngay + $rs->trich_ngay
              ? l(number_format($rs->chi_ban_hang_ngay + $rs->cvt2_ngay + $rs->cl2_ngay + $rs->chi_ngay + $rs->trich_ngay), 'quy_doanh_nghiep/thong_ke_thu_chi/chi_tiet_phieu/chi' . $type . '/trong_ngay/' . $rs->ma_thu_quy)
              : '',
            $rs->tien_bot_sp_ngay + $rs->tien_bot_le_ngay + $rs->bh_ngay
              ? l(number_format($rs->tien_bot_sp_ngay + $rs->tien_bot_le_ngay + $rs->bh_ngay), 'quy_doanh_nghiep/thong_ke_thu_chi/chi_tiet_phieu/bot/trong_ngay/' . $rs->ma_thu_quy)
              : '',
          );
        }
      }
      else {
        if ($ma[0] == 'T') {
          $type = '_';
        } else {
          $type = '_bh';
        }
        $data[] = array(
          //l($rs->ten_vat_tu . ' (' . $rs->ma_vat_tu . ')', 'vat_tu/thong_ke_vat_tu/chi_tiet_vat_tu/den_ngay/' . $rs->ma_vat_tu),
          array('data' => $rs->ten_thu_quy, 'class' => array('text-center')),
          array('data' => number_format($rs->thu_ban_hang_den_thang + $rs->nhan_den_thang + $rs->tsp0_den_thang + $rs->tl0_den_thang - $rs->chi_ban_hang_den_thang - $rs->cvt0_den_thang - $rs->cl0_den_thang - $rs->chi_den_thang + $rs->du - $rs->trich_den_thang), 'class' => array('text-center')),
          $rs->thu_ban_hang_thang + $rs->nhap_thang + $rs->tsp1_thang + $rs->tl1_thang
            ? l(number_format($rs->thu_ban_hang_thang + $rs->nhap_thang + $rs->tsp1_thang + $rs->tl1_thang), 'quy_doanh_nghiep/thong_ke_thu_chi/chi_tiet_phieu/thu' . $type . '/den_ngay/' . $rs->ma_thu_quy)
            : '',
          $rs->chi_ban_hang_thang + $rs->cvt1_thang + $rs->cl1_thang + $rs->chi_thang + $rs->trich_thang
            ? l(number_format($rs->chi_ban_hang_thang + $rs->cvt1_thang + $rs->cl1_thang + $rs->chi_thang + $rs->trich_thang), 'quy_doanh_nghiep/thong_ke_thu_chi/chi_tiet_phieu/chi' . $type . '/den_ngay/' . $rs->ma_thu_quy)
            : '',
          $rs->tien_bot_sp_thang + $rs->tien_bot_le_thang + $rs->bh_thang
            ? l(number_format($rs->tien_bot_sp_thang + $rs->tien_bot_le_thang + $rs->bh_thang), 'quy_doanh_nghiep/thong_ke_thu_chi/chi_tiet_phieu/bot/den_ngay/' . $rs->ma_thu_quy)
            : '',
          $rs->thu_ban_hang_ngay + $rs->nhap_ngay + $rs->tsp2_ngay + $rs->tl2_ngay
            ? l(number_format($rs->thu_ban_hang_ngay + $rs->nhap_ngay + $rs->tsp2_ngay + $rs->tl2_ngay), 'quy_doanh_nghiep/thong_ke_thu_chi/chi_tiet_phieu/thu' . $type . '/trong_ngay/' . $rs->ma_thu_quy)
            : '',
          $rs->chi_ban_hang_ngay + $rs->cvt2_ngay + $rs->cl2_ngay + $rs->chi_ngay + $rs->trich_ngay
            ? l(number_format($rs->chi_ban_hang_ngay + $rs->cvt2_ngay + $rs->cl2_ngay + $rs->chi_ngay + $rs->trich_ngay), 'quy_doanh_nghiep/thong_ke_thu_chi/chi_tiet_phieu/chi' . $type . '/trong_ngay/' . $rs->ma_thu_quy)
            : '',
          $rs->tien_bot_sp_ngay + $rs->tien_bot_le_ngay + $rs->bh_ngay
            ? l(number_format($rs->tien_bot_sp_ngay + $rs->tien_bot_le_ngay + $rs->bh_ngay), 'quy_doanh_nghiep/thong_ke_thu_chi/chi_tiet_phieu/bot/trong_ngay/' . $rs->ma_thu_quy)
            : '',
        );
      }
    }
    return $data;
  }

  function get_thong_ke_thu_chi_thang($start_month, $end_month) {
    $query = db_select('dt_thu_quy', 'tq');
    $query->fields('tq', array('id', 'ma_thu_quy', 'ten_thu_quy', 'thoi_gian'));
    $query->addExpression('(SELECT SUM(n1.tong_tien) FROM {dt_giao_tien} AS n1 WHERE tq.ma_thu_quy = n1.ma_thu_quy_nhan AND n1.thoi_gian >= :n1_start_month AND n1.thoi_gian <= :n1_end_month)', 'nhap_thang', array(':n1_start_month' => $start_month, ':n1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(tsp1.tong_tien) FROM {dt_phieu_thu_thanh_pham} AS tsp1 WHERE tq.ma_thu_quy = tsp1.ma_thu_quy AND tsp1.thoi_gian >= :tsp1_start_month AND tsp1.thoi_gian <= :tsp1_end_month)', 'tsp1_thang', array(':tsp1_start_month' => $start_month, ':tsp1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(tl1.tong_tien) FROM {dt_phieu_thu_le} AS tl1 WHERE tq.ma_thu_quy = tl1.ma_thu_quy AND tl1.thoi_gian >= :tl1_start_month AND tl1.thoi_gian <= :tl1_end_month)', 'tl1_thang', array(':tl1_start_month' => $start_month, ':tl1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(cvt1.tong_tien) FROM {dt_phieu_chi_vat_tu} AS cvt1 WHERE tq.ma_thu_quy = cvt1.ma_thu_quy AND cvt1.thoi_gian >= :cvt1_start_month AND cvt1.thoi_gian <= :cvt1_end_month)', 'cvt1_thang', array(':cvt1_start_month' => $start_month, ':cvt1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(cl1.tong_tien) FROM {dt_phieu_chi_le} AS cl1 WHERE tq.ma_thu_quy = cl1.ma_thu_quy AND cl1.thoi_gian >= :cl1_start_month AND cl1.thoi_gian <= :cl1_end_month)', 'cl1_thang', array(':cl1_start_month' => $start_month, ':cl1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(c1.tong_tien) FROM {dt_giao_tien} AS c1 WHERE tq.ma_thu_quy = c1.ma_thu_quy_chi AND c1.thoi_gian >= :c1_start_month AND c1.thoi_gian <= :c1_end_month)', 'chi_thang', array(':c1_start_month' => $start_month, ':c1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(thu.tong_tien) FROM {dt_phieu_chuyen_tien_hang} AS thu WHERE tq.ma_thu_quy = thu.ma_thu_quy_nhan AND thu.thoi_gian >= :thu_start_month AND thu.thoi_gian <= :thu_end_month)', 'thu_ban_hang_thang', array(':thu_start_month' => $start_month, ':thu_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(chi.tong_tien) FROM {dt_phieu_chuyen_tien_hang} AS chi WHERE tq.ma_thu_quy = chi.ma_nhan_vien_ban_hang AND chi.thoi_gian >= :chi_start_month AND chi.thoi_gian <= :chi_end_month)', 'chi_ban_hang_thang', array(':chi_start_month' => $start_month, ':chi_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(trich.tong_tien) FROM {dt_phieu_trich_quy} AS trich WHERE tq.ma_thu_quy = trich.ma_thu_quy AND trich.thoi_gian >= :trich_start_month AND trich.thoi_gian <= :trich_end_month)', 'trich_thang', array(':trich_start_month' => $start_month, ':trich_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(ttp.tien_bot) FROM {dt_phieu_thu_thanh_pham} AS ttp WHERE tq.ma_thu_quy = ttp.ma_thu_quy AND ttp.thoi_gian >= :ttp_start_month AND ttp.thoi_gian <= :ttp_end_month)', 'thu_tp_thang', array(':ttp_start_month' => $start_month, ':ttp_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(tl.tien_bot) FROM {dt_phieu_thu_le} AS tl WHERE tq.ma_thu_quy = tl.ma_thu_quy AND tl.thoi_gian >= :tl_start_month AND tl.thoi_gian <= :tl_end_month)', 'tl_thang', array(':tl_start_month' => $start_month, ':tl_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(bh.tong_tien - bh.tong_tien_tru - bh.thuc_thu) FROM {dt_ban_hang} AS bh WHERE  bh.uid = tq.ma_thu_quy AND bh.thoi_gian >= :bh_start_month AND bh.thoi_gian <= :bh_end_month)', 'bh_thang', array(':bh_start_month' => $start_month, ':bh_end_month' => $end_month));
    $result = $query->orderBy('tq.ma_thu_quy')->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $ma = $rs->ma_thu_quy;
      if ($ma == 'TQ_001') {
        if (user_access('xem quy ban giam doc')) {
          if ($ma[0] == 'T') {
            $type = '_';
          } else {
            $type = '_bh';
          }
          $data[] = array(
            array('data' => $rs->ten_thu_quy, 'class' => array('text-center')),
//        array('data' => number_format($rs->thu_ban_hang_thang + $rs->nhap_thang + $rs->tsp1_thang + $rs->tl1_thang), 'class' => array('text-center')),
//        array('data' => number_format($rs->chi_ban_hang_thang + $rs->cvt1_thang + $rs->cl1_thang + $rs->chi_thang), 'class' => array('text-center')),
//        array('data' => number_format($rs->thu_tp_thang + $rs->tl_thang), 'class' => array('text-center')),
            $rs->thu_ban_hang_thang + $rs->nhap_thang + $rs->tsp1_thang + $rs->tl1_thang
              ? l(number_format($rs->thu_ban_hang_thang + $rs->nhap_thang + $rs->tsp1_thang + $rs->tl1_thang), 'quy_doanh_nghiep/thong_ke_thu_chi/chi_tiet_phieu/thu' . $type . '/trong_thang/' . $rs->ma_thu_quy)
              : '',
            $rs->chi_ban_hang_thang + $rs->cvt1_thang + $rs->cl1_thang + $rs->chi_thang + $rs->trich_thang
              ? l(number_format($rs->chi_ban_hang_thang + $rs->cvt1_thang + $rs->cl1_thang + $rs->chi_thang + +$rs->trich_thang), 'quy_doanh_nghiep/thong_ke_thu_chi/chi_tiet_phieu/chi' . $type . '/trong_thang/' . $rs->ma_thu_quy)
              : '',
            $rs->thu_tp_thang + $rs->tl_thang + $rs->bh_thang
              ? l(number_format($rs->thu_tp_thang + $rs->tl_thang + $rs->bh_thang), 'quy_doanh_nghiep/thong_ke_thu_chi/chi_tiet_phieu/bot/trong_thang/' . $rs->ma_thu_quy)
              : '',
          );
        }
      }
      else {
        if ($ma[0] == 'T') {
          $type = '_';
        } else {
          $type = '_bh';
        }
        $data[] = array(
          array('data' => $rs->ten_thu_quy, 'class' => array('text-center')),
//        array('data' => number_format($rs->thu_ban_hang_thang + $rs->nhap_thang + $rs->tsp1_thang + $rs->tl1_thang), 'class' => array('text-center')),
//        array('data' => number_format($rs->chi_ban_hang_thang + $rs->cvt1_thang + $rs->cl1_thang + $rs->chi_thang), 'class' => array('text-center')),
//        array('data' => number_format($rs->thu_tp_thang + $rs->tl_thang), 'class' => array('text-center')),
          $rs->thu_ban_hang_thang + $rs->nhap_thang + $rs->tsp1_thang + $rs->tl1_thang
            ? l(number_format($rs->thu_ban_hang_thang + $rs->nhap_thang + $rs->tsp1_thang + $rs->tl1_thang), 'quy_doanh_nghiep/thong_ke_thu_chi/chi_tiet_phieu/thu' . $type . '/trong_thang/' . $rs->ma_thu_quy)
            : '',
          $rs->chi_ban_hang_thang + $rs->cvt1_thang + $rs->cl1_thang + $rs->chi_thang + $rs->trich_thang
            ? l(number_format($rs->chi_ban_hang_thang + $rs->cvt1_thang + $rs->cl1_thang + $rs->chi_thang + +$rs->trich_thang), 'quy_doanh_nghiep/thong_ke_thu_chi/chi_tiet_phieu/chi' . $type . '/trong_thang/' . $rs->ma_thu_quy)
            : '',
          $rs->thu_tp_thang + $rs->tl_thang + $rs->bh_thang
            ? l(number_format($rs->thu_tp_thang + $rs->tl_thang + $rs->bh_thang), 'quy_doanh_nghiep/thong_ke_thu_chi/chi_tiet_phieu/bot/trong_thang/' . $rs->ma_thu_quy)
            : '',
        );
      }
    }
    return $data;
  }

  function get_thiet_lap_gia_luong($gia_luong) {
    unset($gia_luong['stt']);
    unset($gia_luong['ten_to_san_xuat']);
    $query = db_select('dt_gia_luong_to', 'gl');
    $query->fields('gl', array('id', 'ma_to_san_xuat', 'id_gia_luong', 'don_gia'));
    $result = $query->execute();
    $data_lt = array();
    while ($rs = $result->fetchObject()) {
      $data_lt[$rs->ma_to_san_xuat][$rs->id_gia_luong] = $rs;
    }
    $query = db_select('dt_to_san_xuat', 'tsx');
    $query->fields('tsx', array('id', 'ma_to_san_xuat', 'ten_to_san_xuat'));
    $query->condition('tsx.trang_thai', 1);
    $result = $query->orderBy('tsx.ma_to_san_xuat')->execute();
    $data = array();
    $i = 0;
    while ($rs = $result->fetchObject()) {
      $i++;
      $data[$rs->ma_to_san_xuat] = array($i, $rs->ten_to_san_xuat);
      foreach ($gia_luong as $id => $name) {
        if (isset($data_lt[$rs->ma_to_san_xuat][$id])) {
          $gia_luong_to = $data_lt[$rs->ma_to_san_xuat][$id];
          $don_gia = number_format($gia_luong_to->don_gia);
          $data[$rs->ma_to_san_xuat][] = $don_gia . ' <a href="javascript:;" onclick="sua_thiet_lap_to(' . $gia_luong_to->id . ', \'' . $don_gia . '\')">(Sửa)</a>';
        }
        else {
          $data[$rs->ma_to_san_xuat][] = '<a href="javascript:;" onclick="nhap_thiet_lap_to(\'' . $rs->ma_to_san_xuat . '\', ' . $id . ')">Thêm mới</a>';
        }
      }
    }
    return $data;
  }

  function get_gia_luong($get_name = false) {
    $query = db_select('dt_gia_luong', 'gl');
    $query->fields('gl', array('id', 'name'));
    $result = $query->orderBy('gl.id')->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      if ($get_name) {
        $data[$rs->id] = $rs->name . ' <a href="javascript:;" onclick="sua_thiet_lap(' . $rs->id . ', \'' . $rs->name . '\')">(Sửa)</a>';
        ;
      }
      else {
        $data[$rs->id] = $rs;
      }
    }
    return $data;
  }

  function get_gia_luong_theo_to($ma_to_san_xuat) {
    $query = db_select('dt_gia_luong_to', 'glt');
    $query->innerJoin('dt_gia_luong', 'gl', 'glt.id_gia_luong = gl.id');
    $query->fields('glt', array('id', 'don_gia'));
    $query->fields('gl', array('name'));
    $query->condition('glt.ma_to_san_xuat', $ma_to_san_xuat);
    $result = $query->orderBy('gl.name')->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = $rs;
    }
    return $data;
  }

  function insert_thiet_lap_gia_luong($ten_thiet_lap) {
    global $user;
    $thoi_gian = time();
    $query = db_insert('dt_gia_luong');
    $arr = array(
      'uid' => $user->uid,
      'name' => $ten_thiet_lap,
//      'type' => $loai_thiet_lap,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $query->execute();
  }

  function update_thiet_lap_gia_luong($ten_thiet_lap, $id_gia_luong) {
    $query = db_update('dt_gia_luong');
    $arr = array(
      'name' => $ten_thiet_lap,
//      'type' => $loai_thiet_lap,
    );
    $query->fields($arr);
    $query->condition('id', $id_gia_luong);
    $query->execute();
  }

  function insert_thiet_lap_gia_luong_to($ma_to_san_xuat, $id_gia_luong, $don_gia) {
    global $user;
    $thoi_gian = time();
    $query = db_insert('dt_gia_luong_to');
    $arr = array(
      'uid' => $user->uid,
      'ma_to_san_xuat' => $ma_to_san_xuat,
      'id_gia_luong' => $id_gia_luong,
      'don_gia' => $don_gia,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $query->execute();
  }

  function update_thiet_lap_gia_luong_to($don_gia, $id_gia_luong_to) {
    $query = db_update('dt_gia_luong_to');
    $arr = array(
      'don_gia' => $don_gia,
    );
    $query->fields($arr);
    $query->condition('id', $id_gia_luong_to);
    $query->execute();
  }

  function get_thong_ke_luong_thang($time) {
    $query = db_select('dt_luong_to', 'lt');
    $query->fields('lt', array('id', 'ma_to_san_xuat', 'tong_san_xuat', 'tong_luong', 'tong_tru', 'so_du'));
    $query->condition('lt.ngay_thang', date('Y-m', $time));
    $result = $query->orderBy('lt.ma_to_san_xuat')->execute();
    $data_lt = array();
    while ($rs = $result->fetchObject()) {
      $data_lt[$rs->ma_to_san_xuat] = $rs;
    }
    $query = db_select('dt_to_san_xuat', 'tsx');
    $query->fields('tsx', array('id', 'ma_to_san_xuat', 'ten_to_san_xuat'));
    $query->condition('tsx.trang_thai', 1);
    $result = $query->orderBy('tsx.ma_to_san_xuat')->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $tong_san_xuat = '';
      $tong_luong = '';
      $tong_tru = '';
      $so_du = '';
      $link = l('Nhập bảng lương', 'quy_doanh_nghiep/luong_nhan_vien/nhap_luong/' . $rs->id);
      if (isset($data_lt[$rs->ma_to_san_xuat])) {
        $lt = $data_lt[$rs->ma_to_san_xuat];
        $tong_san_xuat = number_format($lt->tong_san_xuat);
        $tong_luong = number_format($lt->tong_luong);
        $tong_tru = number_format($lt->tong_tru);
        $so_du = number_format($lt->so_du);
        $link = l('Xem bảng lương', 'quy_doanh_nghiep/luong_nhan_vien/in_luong/' . $lt->id);
      }
      $data[] = array(
        array('data' => $rs->ten_to_san_xuat),
        array('data' => $tong_san_xuat),
        array('data' => $tong_luong),
        array('data' => $tong_tru),
        array('data' => $so_du),
        $link
      );
    }
    $tong_luong = '';
    $tong_tru = '';
    $link = l('Nhập bảng lương', 'quy_doanh_nghiep/luong_nhan_vien/nhap_luong/0');
    if (isset($data_lt['nhan_vien_khac'])) {
      $lt = $data_lt['nhan_vien_khac'];
      $tong_luong = number_format($lt->tong_luong);
      $link = l('Xem bảng lương', 'quy_doanh_nghiep/luong_nhan_vien/in_luong/' . $lt->id);
    }
    $data[] = array(
      array('data' => 'Các nhân viên khác'),
      array('data' => ''),
      array('data' => $tong_luong),
      array('data' => ''),
      array('data' => ''),
      $link
    );
    return $data;
  }

  function get_txs_theo_id_tsx($id_tsx, $month, $start_date, $end_date) {
    if ($id_tsx) {
      $query = db_select('dt_to_san_xuat', 'tsx');
      $query->fields('tsx', array('id', 'ma_to_san_xuat', 'ten_to_san_xuat'));
      $query->condition('tsx.trang_thai', 1);
      $query->condition('tsx.id', $id_tsx);
      $query->addExpression('(SELECT lt.tong_luong FROM {dt_luong_to} AS lt WHERE lt.ma_to_san_xuat = tsx.ma_to_san_xuat AND lt.ngay_thang = :ngay_thang)', 'tong_luong', array(':ngay_thang' => $month));
      $query->addExpression('(SELECT SUM(td.so_cong_nhat) FROM {dt_tien_do_san_xuat} AS td WHERE td.ma_to_san_xuat = tsx.ma_to_san_xuat AND td.ngay_thang >= :start_date_1 AND td.ngay_thang <= :end_date_1)', 'tong_so_cong', array(':start_date_1' => $start_date, ':end_date_1' => $end_date));
      $query->addExpression('(SELECT SUM(tp.so_tien) FROM {dt_thuong_phat_to_san_xuat} AS tp WHERE tp.ma_to_san_xuat = tsx.ma_to_san_xuat AND tp.ngay_thang >= :start_date_2 AND tp.ngay_thang <= :end_date_2 AND type = :type)', 'tong_so_phat', array(':start_date_2' => $start_date, ':end_date_2' => $end_date, ':type' => 'phat'));
      return $query->execute()->fetchObject();
    }
    else {
      $query = db_select('dt_luong_to', 'lt');
      $query->fields('lt', array('tong_luong'));
      $query->condition('lt.ngay_thang', $month);
      $query->condition('lt.ma_to_san_xuat', 'nhan_vien_khac');
      $result = $query->execute()->fetchObject();
      if (!$result) {
        $result->tong_luong = 0;
      }
      $result->id = 0;
      $result->ma_to_san_xuat = 'nhan_vien_khac';
      $result->ten_to_san_xuat = 'nhân viên khác';
      $result->tong_so_cong = 0;
      $result->tong_so_phat = 0;
      return $result;
    }
  }

  function get_nv_theo_id_tsx($id_tsx, $start_date, $end_date) {
    $query = db_select('dt_nhan_vien', 'nv');
    $query->fields('nv', array('id', 'ma_nhan_vien', 'ten_nhan_vien', 'chuc_vu', 'tien_luu'));
    $query->condition('nv.trang_thai', 1);
    $query->condition('nv.id_tosanxuat', $id_tsx);
    $query->addExpression('(SELECT SUM(tp.so_tien) FROM {dt_thuong_phat_nhan_vien} AS tp WHERE tp.ma_nhan_vien = nv.ma_nhan_vien AND tp.ma_bang_luong = 0  AND type = :type)', 'tong_so_phat', array(':type' => 'phat'));
    $result = $query->execute();
    $data = array();
    $i = 0;
    $chuc_vu = get_option_chuc_vu();
    $ma_nv = array();
    while ($rs = $result->fetchObject()) {
      $i++;
      $ma_nv[] = $rs->ma_nhan_vien;
      $data['rows'][] = array(
        $i . '<input type="hidden" class="tien_luu" name="tien_luu[' . $rs->ma_nhan_vien . ']" value="' . $rs->tien_luu . '" />'
        . '<input type="hidden" class="tong_so_phat" name="tong_so_phat[' . $rs->ma_nhan_vien . ']" value="' . ($rs->tong_so_phat
          ? $rs->tong_so_phat : 0) . '" />',
        $rs->ten_nhan_vien,
        $chuc_vu[$rs->chuc_vu],
        '<input type="text" class="form-text text so_cong" name="so_cong[' . $rs->ma_nhan_vien . ']" value="' . ($_POST
          ? $_POST['so_cong'][$rs->ma_nhan_vien] : '') . '" autocomplete="OFF" />',
        '<input type="text" class="format_currency form-text text gia_cong" name="gia_cong[' . $rs->ma_nhan_vien . ']" value="' . ($_POST
          ? $_POST['gia_cong'][$rs->ma_nhan_vien] : '') . '" autocomplete="OFF" />',
        '<input type="text" class="format_currency form-text text luong_cong_nhat" name="luong_cong_nhat[' . $rs->ma_nhan_vien . ']" value="' . ($_POST
          ? $_POST['luong_cong_nhat'][$rs->ma_nhan_vien] : '') . '" autocomplete="OFF" />',
        '<input type="text" class="format_currency form-text text luong_trach_nhiem" name="luong_trach_nhiem[' . $rs->ma_nhan_vien . ']" value="' . ($_POST
          ? $_POST['luong_trach_nhiem'][$rs->ma_nhan_vien] : '') . '" autocomplete="OFF" />',
        '<input type="text" class="format_currency form-text text tien_an" name="tien_an[' . $rs->ma_nhan_vien . ']" value="' . ($_POST
          ? $_POST['tien_an'][$rs->ma_nhan_vien] : '') . '" autocomplete="OFF" />',
        '<input type="text" class="format_currency form-text text tien_thuong" name="tien_thuong[' . $rs->ma_nhan_vien . ']" value="' . ($_POST
          ? $_POST['tien_thuong'][$rs->ma_nhan_vien] : '') . '" autocomplete="OFF" />',
        number_format($rs->tien_luu),
        '<input type="text" class="format_currency form-text text tien_luu_thang" name="tien_luu_thang[' . $rs->ma_nhan_vien . ']" value="' . ($_POST
          ? $_POST['tien_luu_thang'][$rs->ma_nhan_vien] : number_format($rs->tien_luu)) . '" autocomplete="OFF" />',
        '<a href="javascript:;" onclick="show_phat_nv(\'' . $rs->ma_nhan_vien . '\', \'' . $rs->ten_nhan_vien . '\')">' . number_format($rs->tong_so_phat) . '</a>',
//        '<input type="text" class="format_currency dong_phuc" name="dong_phuc['.$rs->ma_nhan_vien.']" value="'.($_POST ? $_POST['dong_phuc'][$rs->ma_nhan_vien] : '').'" />',
      );
    }
    $data['rows'][] = array(
      array('data' => '(*) Nhập số tiền lưu tháng này', 'colspan' => 12, 'class' => array('text-center'))
    );
    $data['ma_nv'] = $ma_nv;
    return $data;
  }

  function insert_luong_to($uid, $ma_to_san_xuat, $date, $tong_san_luong, $tong_so_luong, $phat_loi_to, $so_du, $time) {
    $query = db_insert('dt_luong_to');
    $arr = array(
      'uid' => $uid,
      'ma_to_san_xuat' => $ma_to_san_xuat,
      'ngay_thang' => $date,
      'tong_san_xuat' => $tong_san_luong,
      'tong_luong' => $tong_so_luong,
      'tong_tru' => $phat_loi_to,
      'so_du' => $so_du,
      'thoi_gian' => $time
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $ma_bl = $query->execute();
    db_update('dt_thuong_phat_to_san_xuat')
      ->fields(array(
                    'ma_bang_luong' => $ma_bl,
               ))
      ->condition('ma_to_san_xuat ', $ma_to_san_xuat)
      ->condition('ma_bang_luong ', 0)
      ->execute();
    return $ma_bl;
  }

  function insert_luong_to_chi_tiet($uid, $ma_to_san_xuat, $date, $san_luong_id_arr, $san_luong_arr, $name_gia_luong_arr, $don_gia_gia_luong_arr, $time) {
    $query = db_insert('dt_luong_to_chi_tiet');
    $first = false;
    foreach ($san_luong_id_arr as $key => $id) {
      $san_luong = (int) str_replace(',', '', $san_luong_arr[$key]);
      $arr = array(
        'uid' => $uid,
        'ma_to_san_xuat' => $ma_to_san_xuat,
        'ngay_thang' => $date,
        'name' => $name_gia_luong_arr[$id],
        'so_luong' => $san_luong,
        'don_gia' => $don_gia_gia_luong_arr[$id],
        'tong_cong' => $san_luong * $don_gia_gia_luong_arr[$id],
        'thoi_gian' => $time
      );
      if (!$first) {
        $query->fields(array_keys($arr));
      }
      $query->values($arr);
      $first = true;
    }
    $query->execute();
  }

  function insert_luong_nv($uid, $date, $so_cong_arr, $gia_cong_arr, $luong_cong_nhat_arr, $luong_trach_nhiem_arr, $tien_thuong_arr, $tien_an_arr, $tien_luu_thang_arr, $tong_so_phat_arr, $time, $ma_to_san_xuat, $ma_bl) {
    $query = db_insert('dt_luong_nhan_vien');
    $ma_thuong_nv_arr = array();
    $tien_thuong_nv_arr = array();
    $ly_do_thuong_nv_arr = array();
    $ngay_thang = date('Y-m-d', $time);
    $ngay_thang_title = date('n/Y', strtotime($date . '-01'));
    $first = false;
    $tong_so_luong = 0;
    foreach ($so_cong_arr as $ma_nhan_vien => $so_cong) {
      $gia_cong = (int) str_replace(',', '', $gia_cong_arr[$ma_nhan_vien]);
      $luong_cong_nhat = $luong_cong_nhat_arr[$ma_nhan_vien]
        ? ((int) str_replace(',', '', $luong_cong_nhat_arr[$ma_nhan_vien])) : 0;
      $luong_trach_nhiem = $luong_trach_nhiem_arr[$ma_nhan_vien]
        ? ((int) str_replace(',', '', $luong_trach_nhiem_arr[$ma_nhan_vien])) : 0;
      $tien_thuong = $tien_thuong_arr[$ma_nhan_vien] ? ((int) str_replace(',', '', $tien_thuong_arr[$ma_nhan_vien]))
        : 0;
      $tien_an = $tien_an_arr[$ma_nhan_vien] ? ((int) str_replace(',', '', $tien_an_arr[$ma_nhan_vien])) : 0;
      $tong_so_phat = (int) $tong_so_phat_arr[$ma_nhan_vien];
      $tien_luu_thang = $tien_luu_thang_arr[$ma_nhan_vien]
        ? ((int) str_replace(',', '', $tien_luu_thang_arr[$ma_nhan_vien])) : 0;
      //      $tien_luu_cuoi_thang = $tien_luu_arr[$ma_nhan_vien] + $tien_luu_thang_arr[$ma_nhan_vien];
      $luong_chinh = $so_cong * $gia_cong;
      $tong_cong = $luong_chinh + $luong_cong_nhat + $luong_trach_nhiem + $tien_thuong - $tien_an - $tong_so_phat;
      module_load_include('inc', 'nhan_vien', 'model/nhan_vien');
      $nhan_vien = get_nhan_vien_theo_ma($ma_nhan_vien);
      $thanh_toan = $tong_cong + $nhan_vien->tien_luu - $tien_luu_thang;
      $tong_so_luong += $luong_chinh;
      $arr = array(
        'uid' => $uid,
        'ma_nhan_vien' => $ma_nhan_vien,
        'ma_to_san_xuat' => $ma_to_san_xuat,
        'ngay_thang' => $date,
        'so_cong' => $so_cong,
        'luong_cong' => $gia_cong,
        'luong_chinh' => $luong_chinh,
        'luong_cong_nhat' => $luong_cong_nhat,
        'luong_trach_nhiem' => $luong_trach_nhiem,
        'tien_thuong' => $tien_thuong,
        'tien_an' => $tien_an,
        'phat_loi' => $tong_so_phat,
        'tong_cong' => $tong_cong,
        'tien_luu' => $tien_luu_thang,
//        'tien_luu_cuoi_thang' => $tien_luu_cuoi_thang,
        'thanh_toan' => $thanh_toan,
        'thoi_gian' => $time
      );
      if (!$first) {
        $query->fields(array_keys($arr));
      }
      $query->values($arr);
      //  if ($tien_luu_thang > 0) {
      db_update('dt_nhan_vien')
//        $query_update->expression('tien_luu', 'tien_luu + :tien_luu', array(':tien_luu' => $tien_luu_thang));
//
//        $query_update->condition('ma_nhan_vien', $ma_nhan_vien);
//        $query_update->execute();
        ->fields(array(
                      'tien_luu' => $tien_luu_thang,
                 ))
        ->condition('ma_nhan_vien', $ma_nhan_vien)
        ->execute();
      db_update('dt_thuong_phat_nhan_vien')
        ->fields(array(
                      'ma_bang_luong' => $ma_bl,
                 ))
        ->condition('ma_nhan_vien ', $ma_nhan_vien)
        ->condition('ma_bang_luong ', 0)
        ->execute();
      //    }
      if ($tien_thuong > 0) {
        $ma_thuong_nv_arr[] = $ma_nhan_vien;
        $tien_thuong_nv_arr[] = $tien_thuong;
        $ly_do_thuong_nv_arr[] = 'Thưởng trong bảng lương tháng ' . $ngay_thang_title;
      }
      $first = true;
    }
    $query->execute();
    if ($ma_to_san_xuat == 'nhan_vien_khac') {
      insert_luong_to($uid, $ma_to_san_xuat, $date, 0, $tong_so_luong, 0, 0, $time);
    }
    if (!empty($ma_thuong_nv_arr)) {
      include_once(drupal_get_path('module', 'nhan_vien') . '/model/nhan_vien.inc');
      $type = 'thuong';
      insert_thuong_phat_nv($type, $ma_thuong_nv_arr, $ngay_thang, $tien_thuong_nv_arr, $ly_do_thuong_nv_arr);
    }
  }

  function get_luong_to($id_lt) {
    $query = db_select('dt_luong_to', 'lt');
    $query->leftJoin('dt_to_san_xuat', 'tsx', 'tsx.ma_to_san_xuat = lt.ma_to_san_xuat');
    $query->fields('tsx', array('ten_to_san_xuat'));
    $query->addField('tsx', 'id', 'id_tsx');
    $query->fields('lt', array('id', 'ma_to_san_xuat', 'ngay_thang', 'tong_san_xuat', 'tong_luong', 'tong_tru', 'so_du'));
    $query->condition('lt.id', $id_lt);
    return $query->execute()->fetchObject();
  }

  function get_luong_to_chi_tiet($ma_to_san_xuat, $ngay_thang) {
    $query = db_select('dt_luong_to_chi_tiet', 'lt');
    $query->fields('lt', array('id', 'name', 'so_luong', 'don_gia', 'tong_cong'));
    $query->condition('lt.ma_to_san_xuat', $ma_to_san_xuat);
    $query->condition('lt.ngay_thang', $ngay_thang);
    $result = $query->orderBy('lt.name')->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = $rs;
    }
    return $data;
  }

  function get_luong_nv($ma_to_san_xuat, $ngay_thang) {
    $query = db_select('dt_luong_nhan_vien', 'lt');
    $query->innerJoin('dt_nhan_vien', 'nv', 'nv.ma_nhan_vien = lt.ma_nhan_vien');
    $query->fields('nv', array('ma_nhan_vien', 'ten_nhan_vien', 'tien_luu_dau'));
    $query->fields('lt', array('id', 'so_cong', 'luong_cong', 'luong_chinh', 'luong_cong_nhat', 'luong_trach_nhiem', 'tien_thuong', 'tien_an', 'phat_loi', 'tong_cong', 'tien_luu', 'thanh_toan'));
    $query->condition('lt.ma_to_san_xuat', $ma_to_san_xuat);
    $query->condition('lt.ngay_thang', $ngay_thang);
    $query->addExpression('(SELECT SUM(lnv.tien_luu) FROM {dt_luong_nhan_vien} AS lnv WHERE lnv.ma_nhan_vien = nv.ma_nhan_vien AND lnv.ngay_thang < :start_date_2)', 'tien_luu_thang', array(':start_date_2' => $ngay_thang));
    $result = $query->orderBy('lt.ma_nhan_vien')->execute();
    $data = array();
    $data[] = array(
      // array('data' => 'STT', 'rowspan' => 2),
      array('data' => 'Họ tên', 'rowspan' => 2),
      array('data' => 'Lương chính', 'colspan' => 3, 'class' => array('text-center')),
      array('data' => 'Lương công nhật', 'rowspan' => 2),
      array('data' => 'Lương trách nhiệm', 'rowspan' => 2),
      array('data' => 'Thưởng', 'rowspan' => 2),
      array('data' => 'Giảm trừ', 'colspan' => 2, 'class' => array('text-center')),
      array('data' => 'Tổng cộng', 'rowspan' => 2),
      array('data' => 'Dư ĐT', 'rowspan' => 2),
      array('data' => 'Tiền lưu', 'rowspan' => 2),
      array('data' => 'Thanh toán', 'rowspan' => 2),
      // array('data' => 'Dư CT', 'rowspan' => 2),
    );
    $data[] = array(
      'Số công',
      'Lương/1C',
      'Tổng cộng',
      'Tiền ăn',
      'Phạt lỗi',
    );
    $i = 0;
    $tong_luong_chinh = 0;
    $tong_luong_cong_nhat = 0;
    $tong_luong_trach_nhiem = 0;
    $tong_tien_thuong = 0;
    $tong_tien_an = 0;
    $tong_phat_loi = 0;
    $tong_cong = 0;
    $tong_dau_thang = 0;
    $tong_tien_luu = 0;
    $tong_thanh_toan = 0;
    $tong_cuoi_thang = 0;
    while ($rs = $result->fetchObject()) {
      //$i++;
      $dau_thang = $rs->tien_luu_thang ? $rs->tien_luu_thang : 0;
      $dau_thang += $rs->tien_luu_dau;
      $cuoi_thang = $rs->tien_luu;
      $data[] = array(
        //  $i,
        $rs->ten_nhan_vien,
        $rs->so_cong,
        number_format($rs->luong_cong),
        number_format($rs->luong_chinh),
        number_format($rs->luong_cong_nhat),
        number_format($rs->luong_trach_nhiem),
        number_format($rs->tien_thuong),
        number_format($rs->tien_an),
        '<a href="javascript:;" onclick="show_phat_nv(\'' . $rs->ma_nhan_vien . '\', \'' . $rs->ten_nhan_vien . '\')">' . number_format($rs->phat_loi) . '</a>',
        // number_format($rs->phat_loi),
        number_format($rs->tong_cong),
        number_format($dau_thang),
        number_format($rs->tien_luu),
        number_format($rs->thanh_toan),
        // number_format($cuoi_thang),
      );
      $tong_luong_chinh += $rs->luong_chinh;
      $tong_luong_cong_nhat += $rs->luong_cong_nhat;
      $tong_luong_trach_nhiem += $rs->luong_trach_nhiem;
      $tong_tien_thuong += $rs->tien_thuong;
      $tong_tien_an += $rs->tien_an;
      $tong_phat_loi += $rs->phat_loi;
      $tong_cong += $rs->tong_cong;
      $tong_dau_thang += $dau_thang;
      $tong_tien_luu += $rs->tien_luu;
      $tong_thanh_toan += $rs->thanh_toan;
      $tong_cuoi_thang += $cuoi_thang;
    }
    $data[] = array(
      'data' => array(
        array('data' => 'Tổng cộng', 'colspan' => 3, 'class' => array('text-right', 'text-bold')),
        number_format($tong_luong_chinh),
        number_format($tong_luong_cong_nhat),
        number_format($tong_luong_trach_nhiem),
        number_format($tong_tien_thuong),
        number_format($tong_tien_an),
        number_format($tong_phat_loi),
        number_format($tong_cong),
        number_format($tong_dau_thang),
        number_format($tong_tien_luu),
        number_format($tong_thanh_toan),
        // number_format($tong_cuoi_thang),
      ),
    );
    return $data;
  }

  function get_loi_nhan_vien_by_month($nhan_vien, $start_date, $end_date) {
    $query = db_select('dt_thuong_phat_nhan_vien', 'tp');
    $query->fields('tp', array('ma_nhan_vien', 'ngay_thang', 'so_tien', 'ly_do', 'ma_bang_luong'));
    //$query->condition('tp.ngay_thang', $start_date, '>=');
    //$query->condition('tp.ngay_thang', $end_date, '<=');
    $query->condition('tp.ma_bang_luong', 0, '=');
    $query->condition('tp.ma_nhan_vien', $nhan_vien, 'in');
    $query->condition('tp.type', 'phat', '=');
    $result = $query->orderBy('tp.ngay_thang')->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[$rs->ma_nhan_vien][] = array(
        date('d/m/Y', strtotime($rs->ngay_thang)),
        number_format($rs->so_tien),
        $rs->ly_do
      );
    }
    return $data;
  }

  function get_loi_nhan_vien_by_month_theo_ma_bl($nhan_vien, $ma_bl) {
    $query = db_select('dt_thuong_phat_nhan_vien', 'tp');
    $query->fields('tp', array('ma_nhan_vien', 'ngay_thang', 'so_tien', 'ly_do', 'ma_bang_luong'));
    // $query->condition('tp.ngay_thang', $start_date, '>=');
    // $query->condition('tp.ngay_thang', $end_date, '<=');
    $query->condition('tp.ma_bang_luong', $ma_bl, '=');
    $query->condition('tp.ma_nhan_vien', $nhan_vien, 'in');
    $query->condition('tp.type', 'phat', '=');
    $result = $query->orderBy('tp.ngay_thang')->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[$rs->ma_nhan_vien][] = array(
        date('d/m/Y', strtotime($rs->ngay_thang)),
        number_format($rs->so_tien),
        $rs->ly_do
      );
    }
    return $data;
  }

  function get_loi_to_by_month($tsx, $start_date, $end_date) {
    $query = db_select('dt_thuong_phat_to_san_xuat', 'tp');
    $query->fields('tp', array('ma_to_san_xuat', 'ngay_thang', 'so_tien', 'ly_do', 'ma_bang_luong'));
    //$query->condition('tp.ngay_thang', $start_date, '>=');
    //$query->condition('tp.ngay_thang', $end_date, '<=');
    $query->condition('tp.ma_bang_luong', 0, '=');
    $query->condition('tp.ma_to_san_xuat', $tsx);
    $result = $query->orderBy('tp.ngay_thang')->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
        date('d/m/Y', strtotime($rs->ngay_thang)),
        number_format($rs->so_tien),
        $rs->ly_do
      );
    }
    return $data;
  }

  function get_loi_to_by_month_theo_ma_bl($tsx, $ma_bl) {
    $query = db_select('dt_thuong_phat_to_san_xuat', 'tp');
    $query->fields('tp', array('ma_to_san_xuat', 'ngay_thang', 'so_tien', 'ly_do'));
    // $query->condition('tp.ngay_thang', $start_date, '>=');
    //  $query->condition('tp.ngay_thang', $end_date, '<=');
    $query->condition('tp.ma_bang_luong', $ma_bl, '=');
    $query->condition('tp.ma_to_san_xuat', $tsx);
    $result = $query->orderBy('tp.ngay_thang')->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
        date('d/m/Y', strtotime($rs->ngay_thang)),
        number_format($rs->so_tien),
        $rs->ly_do
      );
    }
    return $data;
  }

  function check_tien_du_thu_quy($ma_thu_quy) {
    $query = db_select('dt_thu_quy', 'tq');
    $query->fields('tq', array('tong_tien'));
    $query->condition('tq.ma_thu_quy', $ma_thu_quy);
    $result = $query->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data = $rs->tong_tien;
    }
    return $data;
  }

  function get_thong_ke_chi_tiet_thu_quy($tq, $type, $start_time, $end_time) {
    switch ($type) {
      case 'thu_':
        $query_x = db_select('dt_phieu_chuyen_tien_hang', 'pcth');
        $query_x->condition('pcth.ma_thu_quy_nhan', $tq->ma_thu_quy, '=');
        $query_x->fields('pcth', array('id', 'tong_tien', 'thoi_gian'));
        $query_x->condition('pcth.thoi_gian', $start_time, '>=');
        $query_x->condition('pcth.thoi_gian', $end_time, '<=');
        $query_x->addField('pcth', 'ma_phieu_chuyen', 'ma_phieu');
        $query_x->leftJoin('dt_thu_quy', 'tp1', 'tp1.ma_thu_quy = pcth.ma_thu_quy_nhan');
        $query_x->addField('tp1', 'ten_thu_quy', 'nguoi_nhan');
        $query_x->leftJoin('dt_thu_quy', 'tp2', 'tp2.ma_thu_quy = pcth.ma_nhan_vien_ban_hang');
        $query_x->addField('tp2', 'ten_thu_quy', 'nguoi_chuyen');
        $query_x->addExpression("'Chuyển tiền hàng'", 'loai');
        $query_x->addExpression("'giaotienbanhang'", 'link');
        $query_n = db_select('dt_giao_tien', 'gt');
        $query_n->condition('gt.ma_thu_quy_nhan', $tq->ma_thu_quy, '=');
        $query_n->fields('gt', array('id', 'tong_tien', 'thoi_gian'));
        $query_n->condition('gt.thoi_gian', $start_time, '>=');
        $query_n->condition('gt.thoi_gian', $end_time, '<=');
        $query_n->addField('gt', 'ma_phieu_chuyen', 'ma_phieu');
        $query_n->leftJoin('dt_thu_quy', 'tq1', 'tq1.ma_thu_quy = gt.ma_thu_quy_nhan');
        $query_n->addField('tq1', 'ten_thu_quy', 'nguoi_nhan');
        $query_n->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = gt.ma_thu_quy_chi');
        $query_n->addField('tq', 'ten_thu_quy', 'nguoi_chuyen');
        $query_n->addExpression("'Chuyển tiền'", 'loai');
        $query_n->addExpression("'chuyentien'", 'link');
        $query_x->union($query_n, 'UNION ALL');
        $query = db_select($query_x, 'vt')->extend('PagerDefault')->extend('TableSort');
        $query->fields('vt', array('ma_phieu', 'nguoi_nhan', 'nguoi_chuyen', 'tong_tien', 'thoi_gian', 'loai', 'link'));
        $result = $query->orderBy('thoi_gian', 'DESC')->limit(20)->execute();
        $data = array();
        $header = array(
          'Mã phiếu',
          'Loại phiếu',
          'Người nhận',
          'Người chuyển',
          'Số tiền',
          'Thời gian',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $data[] = array(
            array('data' => $rs->ma_phieu, 'class' => array('text-center')),
            array('data' => $rs->loai, 'class' => array('text-center')),
            array('data' => $rs->nguoi_nhan, 'class' => array('text-center')),
            array('data' => $rs->nguoi_chuyen, 'class' => array('text-center')),
            array('data' => number_format($rs->tong_tien), 'class' => array('text-center')),
            array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('text-center')),
            l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/' . $rs->link . '/' . $rs->ma_phieu),
          );
        }
        break;
      case 'thu_bh':
        $query_x = db_select('dt_phieu_thu_thanh_pham', 'ttp');
        $query_x->condition('ttp.ma_thu_quy', $tq->ma_thu_quy, '=');
        $query_x->fields('ttp', array('id', 'tong_tien_chua_giam', 'tong_tien', 'tien_bot', 'thanh_toan', 'thoi_gian'));
        $query_x->condition('ttp.thoi_gian', $start_time, '>=');
        $query_x->condition('ttp.thoi_gian', $end_time, '<=');
        $query_x->addField('ttp', 'ma_phieu_thu', 'ma_phieu');
        $query_x->leftJoin('dt_khach_hang', 'kd', 'kd.ma_khach_hang = ttp.ma_khach_hang');
        $query_x->addField('kd', 'ten_khach_hang', 'nguoi_nop');
        $query_x->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = ttp.ma_thu_quy');
        $query_x->addField('tq', 'ten_thu_quy', 'nguoi_nhan');
        $query_x->addExpression("'Thu bán hàng'", 'loai');
        $query_x->addExpression("'thusanpham'", 'link');
        $query_n = db_select('dt_phieu_thu_le', 'tl');
        $query_n->condition('tl.ma_thu_quy', $tq->ma_thu_quy, '=');
        $query_n->fields('tl', array('id', 'tong_tien_chua_giam', 'tong_tien', 'tien_bot', 'thanh_toan', 'thoi_gian'));
        $query_n->condition('tl.thoi_gian', $start_time, '>=');
        $query_n->condition('tl.thoi_gian', $end_time, '<=');
        $query_n->addField('tl', 'ma_phieu_thu', 'ma_phieu');
        $query_n->addField('tl', 'nguoi_nop');
        $query_n->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = tl.ma_thu_quy');
        $query_n->addField('tq', 'ten_thu_quy', 'nguoi_nhan');
        $query_n->addExpression("'Thu lẻ'", 'loai');
        $query_n->addExpression("'thule'", 'link');
        $query_x->union($query_n, 'UNION ALL');
        $query = db_select($query_x, 'vt')->extend('PagerDefault')->extend('TableSort');
        $query->fields('vt', array('ma_phieu', 'nguoi_nop', 'tong_tien_chua_giam', 'tong_tien', 'tien_bot', 'nguoi_nhan', 'thanh_toan', 'thoi_gian', 'loai', 'link'));
        $result = $query->orderBy('thoi_gian', 'DESC')->limit(20)->execute();
        $data = array();
        $header = array(
          'Mã phiếu thu',
          'Loại phiếu',
          'Khách hàng',
          'Số tiền trước giảm',
          'Thực thu',
          'Tiền bớt',
          'Thủ quỹ thu',
          'Thanh toán',
          'Ngày tháng',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $data[] = array(
            array('data' => $rs->ma_phieu, 'class' => array('text-center')),
            array('data' => $rs->loai, 'class' => array('text-center')),
            array('data' => $rs->nguoi_nop, 'class' => array('text-center')),
            array('data' => number_format($rs->tong_tien_chua_giam), 'class' => array('text-center')),
            array('data' => number_format($rs->tong_tien), 'class' => array('text-center')),
            array('data' => number_format($rs->tien_bot), 'class' => array('text-center')),
            array('data' => $rs->nguoi_nhan, 'class' => array('text-center')),
            array('data' => $rs->thanh_toan ? 'Đã chuyển tiền' : 'Chưa chuyển tiền', 'class' => array('text-center')),
            array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('text-center')),
            l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/' . $rs->link . '/' . $rs->ma_phieu),
          );
        }
        break;
      case 'chi_':
        $query_x = db_select('dt_phieu_chi_vat_tu', 'cvt');
        $query_x->condition('cvt.ma_thu_quy', $tq->ma_thu_quy, '=');
        $query_x->fields('cvt', array('id', 'tong_tien', 'thoi_gian'));
        $query_x->condition('cvt.thoi_gian', $start_time, '>=');
        $query_x->condition('cvt.thoi_gian', $end_time, '<=');
        $query_x->addField('cvt', 'ma_phieu_chi', 'ma_phieu');
        $query_x->leftJoin('dt_nha_cung_cap', 'ncc', 'ncc.ma_nha_cung_cap = cvt.ma_nha_cung_cap');
        $query_x->addField('ncc', 'ten_nha_cung_cap', 'nguoi_nhan');
        $query_x->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = cvt.ma_thu_quy');
        $query_x->addField('tq', 'ten_thu_quy', 'nguoi_chi');
        $query_x->addExpression("'Chi vật tư'", 'loai');
        $query_x->addExpression("'chivattu'", 'link');
        $query_n = db_select('dt_phieu_chi_le', 'cl');
        $query_n->condition('cl.ma_thu_quy', $tq->ma_thu_quy, '=');
        $query_n->fields('cl', array('id', 'tong_tien', 'thoi_gian'));
        $query_n->condition('cl.thoi_gian', $start_time, '>=');
        $query_n->condition('cl.thoi_gian', $end_time, '<=');
        $query_n->addField('cl', 'ma_phieu_chi', 'ma_phieu');
        $query_n->addField('cl', 'nguoi_nhan');
        $query_n->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = cl.ma_thu_quy');
        $query_n->addField('tq', 'ten_thu_quy', 'nguoi_chi');
        $query_n->addExpression("'Chi lẻ'", 'loai');
        $query_n->addExpression("'chilẻ'", 'link');
        $query_t = db_select('dt_giao_tien', 'gt');
        $query_t->condition('gt.ma_thu_quy_chi', $tq->ma_thu_quy, '=');
        $query_t->fields('gt', array('id', 'tong_tien', 'thoi_gian'));
        $query_t->condition('gt.thoi_gian', $start_time, '>=');
        $query_t->condition('gt.thoi_gian', $end_time, '<=');
        $query_t->addField('gt', 'ma_phieu_chuyen', 'ma_phieu');
        $query_t->leftJoin('dt_thu_quy', 'tq1', 'tq1.ma_thu_quy = gt.ma_thu_quy_nhan');
        $query_t->addField('tq1', 'ten_thu_quy', 'nguoi_nhan');
        $query_t->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = gt.ma_thu_quy_chi');
        $query_t->addField('tq', 'ten_thu_quy', 'nguoi_chi');
        $query_t->addExpression("'Chuyển tiền'", 'loai');
        $query_t->addExpression("'chuyentien'", 'link');
        $query_tr = db_select('dt_phieu_trich_quy', 'trq');
        $query_tr->condition('trq.ma_thu_quy', $tq->ma_thu_quy, '=');
        $query_tr->fields('trq', array('id', 'tong_tien', 'thoi_gian'));
        $query_tr->condition('trq.thoi_gian', $start_time, '>=');
        $query_tr->condition('trq.thoi_gian', $end_time, '<=');
        $query_tr->addField('trq', 'ma_phieu_trich', 'ma_phieu');
        $query_tr->addField('trq', 'nguoi_nhan');
        $query_tr->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = trq.ma_thu_quy');
        $query_tr->addField('tq', 'ten_thu_quy', 'nguoi_chi');
        $query_tr->addExpression("'Trích quỹ'", 'loai');
        $query_tr->addExpression("'trichquy'", 'link');
        $query_x->union($query_n, 'UNION ALL');
        $query_x->union($query_t, 'UNION ALL');
        $query_x->union($query_tr, 'UNION ALL');
        $query = db_select($query_x, 'vt')->extend('PagerDefault')->extend('TableSort');
        $query->fields('vt', array('ma_phieu', 'nguoi_chi', 'nguoi_nhan', 'tong_tien', 'thoi_gian', 'loai', 'link'));
        $result = $query->orderBy('thoi_gian', 'DESC')->limit(20)->execute();
        $data = array();
        $header = array(
          'Mã phiếu chuyển',
          'Loại phiếu',
          'Người chi',
          'Người nhận',
          'Số tiền',
          'Ngày tháng',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $data[] = array(
            array('data' => $rs->ma_phieu, 'class' => array('text-center')),
            array('data' => $rs->loai, 'class' => array('text-center')),
            array('data' => $rs->nguoi_chi, 'class' => array('text-center')),
            array('data' => $rs->nguoi_nhan, 'class' => array('text-center')),
            array('data' => number_format($rs->tong_tien), 'class' => array('text-center')),
            array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('text-center')),
            l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/' . $rs->link . '/' . $rs->ma_phieu),
          );
        }
        break;
      case 'chi_bh':
        $query = db_select('dt_phieu_chuyen_tien_hang', 'pcth')->extend('PagerDefault')->extend('TableSort');
        $query->condition('pcth.ma_nhan_vien_ban_hang', $tq->ma_thu_quy, '=');
        $query->fields('pcth', array('id', 'ma_phieu_chuyen', 'ma_thu_quy_nhan', 'ma_nhan_vien_ban_hang', 'tong_tien', 'thoi_gian'));
        $query->condition('pcth.thoi_gian', $start_time, '>=');
        $query->condition('pcth.thoi_gian', $end_time, '<=');
        $query->leftJoin('dt_thu_quy', 'tp1', 'tp1.ma_thu_quy = pcth.ma_thu_quy_nhan');
        $query->addField('tp1', 'ten_thu_quy', 'ten_thu_quy_nhan');
        $query->leftJoin('dt_thu_quy', 'tp2', 'tp2.ma_thu_quy = pcth.ma_nhan_vien_ban_hang');
        $query->addField('tp2', 'ten_thu_quy', 'ten_nhan_vien_ban_hang');
        $query->addExpression("'Chuyển tiền hàng'", 'loai');
        $result = $query->orderBy('pcth.thoi_gian', 'DESC')->limit(20)->execute();
        $data = array();
        $header = array(
          'Mã phiếu',
          'Loại phiếu',
          'Thủ quỹ nhận',
          'Nhân viên bán hàng',
          'Số tiền',
          'Thời gian',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $data[] = array(
            array('data' => $rs->ma_phieu_chuyen, 'class' => array('text-center')),
            array('data' => $rs->loai, 'class' => array('text-center')),
            array('data' => $rs->ten_thu_quy_nhan, 'class' => array('text-center')),
            array('data' => $rs->ten_nhan_vien_ban_hang, 'class' => array('text-center')),
            array('data' => number_format($rs->tong_tien), 'class' => array('text-center')),
            array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('text-center')),
            l('Chi tiết', 'quy_doanh_nghiep/phieu_thu_chi/in_phieu/giaotienbanhang/' . $rs->ma_phieu_chuyen),
          );
        }
        break;
      case 'bot':
        $query_x = db_select('dt_phieu_thu_thanh_pham', 'ttp');
        $query_x->condition('ttp.ma_thu_quy', $tq->ma_thu_quy, '=');
        $query_x->fields('ttp', array('id', 'thoi_gian', 'tien_bot'));
        $query_x->condition('ttp.thoi_gian', $start_time, '>=');
        $query_x->condition('ttp.thoi_gian', $end_time, '<=');
        $query_x->condition('ttp.tien_bot', 0, '>');
        $query_x->addField('ttp', 'ma_phieu_thu', 'ma_phieu');
        $query_x->leftJoin('dt_khach_hang', 'kd', 'kd.ma_khach_hang = ttp.ma_khach_hang');
        $query_x->addField('kd', 'ten_khach_hang', 'nguoi_nop');
        $query_x->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = ttp.ma_thu_quy');
        $query_x->addField('tq', 'ten_thu_quy', 'nguoi_nhan');
        $query_x->addExpression("'Thu bán hàng'", 'loai');
        $query_x->addExpression("'quy_doanh_nghiep/phieu_thu_chi/in_phieu/thusanpham/'", 'link');
        $query_n = db_select('dt_phieu_thu_le', 'tl');
        $query_n->condition('tl.ma_thu_quy', $tq->ma_thu_quy, '=');
        $query_n->fields('tl', array('id', 'thoi_gian', 'tien_bot'));
        $query_n->condition('tl.thoi_gian', $start_time, '>=');
        $query_n->condition('tl.thoi_gian', $end_time, '<=');
        $query_n->condition('tl.tien_bot', 0, '>');
        $query_n->addField('tl', 'ma_phieu_thu', 'ma_phieu');
        $query_n->addField('tl', 'nguoi_nop');
        $query_n->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = tl.ma_thu_quy');
        $query_n->addField('tq', 'ten_thu_quy', 'nguoi_nhan');
        $query_n->addExpression("'Thu lẻ'", 'loai');
        $query_n->addExpression("'quy_doanh_nghiep/phieu_thu_chi/in_phieu/thule/'", 'link');
        $query_b = db_select('dt_ban_hang', 'bh');
        $query_b->condition('bh.uid', $tq->ma_thu_quy, '=');
        $query_b->fields('bh', array('id', 'thoi_gian', 'tien_bot'));
        //$query_b->addExpression('(SELECT SUM(pbh.tong_tien - pbh.tong_tien_tru - pbh.thuc_thu) FROM {dt_ban_hang} AS pbh WHERE  pbh.id=bh.id )', 'tien_bot');
        $query_b->condition('bh.thoi_gian', $start_time, '>=');
        $query_b->condition('bh.thoi_gian', $end_time, '<=');
        $query_b->condition('bh.tien_bot', 0, '>');
        $query_b->addField('bh', 'ma_phieu_ban_hang', 'ma_phieu');
        $query_b->addField('bh', 'ma_khach_hang', 'nguoi_nop');
        $query_b->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = bh.uid');
        $query_b->addField('tq', 'ten_thu_quy', 'nguoi_nhan');
        $query_b->addExpression("'Bán hàng'", 'loai');
        $query_b->addExpression("'san_pham/ban_hang/in_phieu_ban_hang/ban_hang/'", 'link');
        $query_x->union($query_n, 'UNION ALL');
        $query_x->union($query_b, 'UNION ALL');
        $query = db_select($query_x, 'vt')->extend('PagerDefault')->extend('TableSort');
        $query->fields('vt', array('ma_phieu', 'nguoi_nop', 'tien_bot', 'nguoi_nhan', 'thoi_gian', 'loai', 'link'));
        $result = $query->orderBy('thoi_gian', 'DESC')->limit(20)->execute();
        $data = array();
        $header = array(
          'Mã phiếu',
          'Loại phiếu',
          'Khách hàng',
          //   'Số tiền',
          //   'Thực thu',
          'Tiền bớt',
          'Thủ quỹ',
          // 'Thanh toán',
          'Ngày tháng',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $data[] = array(
            array('data' => $rs->ma_phieu, 'class' => array('text-center')),
            array('data' => $rs->loai, 'class' => array('text-center')),
            array('data' => $rs->nguoi_nop, 'class' => array('text-center')),
            // array('data' => number_format($rs->tong_tien_chua_giam), 'class' => array('text-center')),
            // array('data' => number_format($rs->tong_tien), 'class' => array('text-center')),
            array('data' => number_format($rs->tien_bot), 'class' => array('text-center')),
            array('data' => $rs->nguoi_nhan, 'class' => array('text-center')),
            // array('data' => $rs->thanh_toan ? 'Đã chuyển tiền' : 'Chưa chuyển tiền', 'class' => array('text-center')),
            array('data' => date('H:i:s d/m/Y', $rs->thoi_gian), 'class' => array('text-center')),
            l('Chi tiết', $rs->link . $rs->ma_phieu),
          );
        }
        break;
    }
    return array('header' => $header, 'rows' => $data);
  }

  function get_phieu_trich_quy($header) {
    $query = db_select('dt_phieu_trich_quy', 'tquy')->extend('PagerDefault')->extend('TableSort');
    $query->fields('tquy', array('id', 'uid', 'ma_phieu_trich', 'ma_thu_quy', 'tong_tien', 'nguoi_nhan', 'ghi_chu', 'thoi_gian'));
    $query->leftJoin('dt_thu_quy', 'tq', 'tq.ma_thu_quy = tquy.ma_thu_quy');
    $query->addField('tq', 'ten_thu_quy');
    $query->orderByHeader($header);
    $result = $query->limit(20)->execute();
    $admin_perm = user_access('admin xoa quy');
    $admin_perm_edit = user_access('admin sua quy');
    $data = array();
    while ($rs = $result->fetchObject()) {
      $output = '<a href="../quy_doanh_nghiep/phieu_thu_chi/xoa_phieu/trichquy/' . $rs->ma_phieu_trich . '" onclick="return check_xoa();">Xóa</a>';
      $data[] = array(
        'ma_phieu_trich' => $rs->ma_phieu_trich,
        'ten_thu_quy' => $rs->ten_thu_quy,
        'tong_tien' => number_format($rs->tong_tien),
        'nguoi_nhan' => $rs->nguoi_nhan,
        'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
        'thao_tac' => ($admin_perm ? ($output . ' | ')
          : '') .
                      ($admin_perm_edit
                        ? (l('Sửa', 'quy_doanh_nghiep/thu_chi/sua_phieu/trichquy/' . $rs->ma_phieu_trich))
                        : '') . dt_view_ghi_chu('vt_' . $rs->id, 'Phiếu : ' . $rs->ma_phieu_trich, $rs->ghi_chu)
      );
    }
    return $data;
  }